Customization and programming tricks by Ronny Van der Snickt

Unshare accounts in MS CRM

After a company worked for several months with MS CRM, they decided to change security. They didn’t wanted user to be able to share accounts anymore. So far no problem but they wanted to remove all shares made by users.

There is no easy way of doing this, They have +60k accounts and 100 users and it’s impossible to check them all manually. So I had to make a program to unshared the records. My first problem was that it’s not possible to view the shares of a record (Correct me if I’m wrong) through the MS CRM Web Service. I could start and try to unshared all users for all records but that would require over 6.000.000 transactions.

So I looked at the database and tried to find a way to retrieve the share information (This is of course unsupported but my only intention here is to retrieve data from the database.)

After a while I found that executing the following SQL would give me all the sharing information I needed.

select POA.ObjectId, sup.SystemUserId from PrincipalObjectAccess POA join SystemUserPrincipals sup on POA.PrincipalId = sup.PrincipalId where POA.ObjectTypeCode = 1 AND
  ((POA.AccessRightsMask | POA.InheritedAccessRightsMask)
  & 1)=1


The ObjectId is the guid of the account and SystemUserId is the users guid to how it is shared to. (There where no teams in the system so all shares where made between users)

After founding this it was easy to write a program that would first load the shares and then unshared them using the RevokeAccessRequest message.


Comment from Augi
Time: January 14, 2010, 4:13 am

Hello Ronny,
I was wondering if you could share you program. With above SQL script it does give me the Accounts that are shared, but I can’t unshare it .
I would appreciate your feedback .

Write a comment