How to – Find shared records in CRM

To find all the records(say lead)  shared with any particular user we can make use of following query

It will return us all records(lead) shared with the user directly( i.e. through Action–>Sharing and user)

select  fl.subject

from
PrincipalObjectAccess poa , FilteredLead fl, FilteredSystemUser fsu
where
poa.ObjectTypeCode = 4
and poa.ObjectId = fl.leadid
and poa.PrincipalId = fsu.systemuserid
and fsu.domainname=SYSTEM_USER

// fsu.fullname=’name of user’

And to get the records shared with the user indirectly (i.e. through Action–>Sharing and Team (user belongs to that team)

select  fl.subject
from
FilteredLead fl,  PrincipalObjectAccess poa,  FilteredTeam ft
where
poa.ObjectTypeCode = 4 

and poa.ObjectId =fl.leadid
and poa.PrincipalId = ft.teamid and 

fl.owneridname not in (select fullname from filteredsystemuser
where domainname=SYSTEM_USER)
and
ft.teamid  in (select ft.teamid from filteredteammembership fm
, filteredsystemuser fsu, filteredteam ft
where fm.systemuserid=fsu.systemuserid and
ft.teamid=fm.teamid
and fsu.domainname=SYSTEM_USER
)

Bye

Author: Nishant Rana

I love working in and sharing everything about Microsoft.NET technology ! View all posts by Nishant Rana