Thursday, August 2, 2012

Share/Un-share bulk records in CRM


CRM has a feature where we can share bulk records with users at one time. But if we have achieved the same in some automation process, CRM service will not respond (or it will take more time, CRM may stop working for some time).  

I had a similar requirement like when sale rep moves from one city to another provide him/her the access of all records belongs to the new city and unshared all records were shared with him/her before. To achieve the same I wrote an SQL query to do the same. 


Declare @SystemUser nvarchar(100)
Declare @ObjectId nvarchar(100)
DECLARE @Count INT

set @SystemUser = (select systemuserid from systemuser where Name ='CRM sales Rep')

declare Cur_SHAREWITHUSER  cursor for 
Select accountId from account where Account.Address1_City = 'Bangalore'

open Cur_SHAREWITHUSER 
fetch Cur_SHAREWITHUSER  into @ObjectId
while(@@fetch_status=0)
BEGIN
SET @Count = 0
SET @Count = (SELECT COUNT(1) FROM PrincipalObjectAccess WHERE  ObjectId = @ObjectId AND PrincipalId = @SystemUser)
IF(@Count >0)
BEGIN
UPDATE PrincipalObjectAccess 
SET AccessRightsMask= 23, -- define access level 
InheritedAccessRightsMask=134217751,
ChangedOn = GETUTCDATE()
WHERE ObjectId = @ObjectId AND PrincipalId = @SystemUser
END
ELSE 
BEGIN
INSERT INTO PrincipalObjectAccess  
(Principalid,ObjectId,ObjectTypeCode,[PrincipalTypeCode],[AccessRightsMask],ChangedOn,
[InheritedAccessRightsMask] ,PrincipalObjectAccessId )
values (@SystemUser , @ObjectId ,1,8,23,GETDATE(),134217751,NEWID())
END 
fetch Cur_SHAREWITHUSER  into @ObjectId
END 


close Cur_SHAREWITHUSER 
deallocate Cur_SHAREWITHUSER 

No comments: