Nishant Rana's Weblog

Home | Pages | Archives

How to – Find shared records in CRM

October 11, 2007 12:09 PM

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

Posted by Nishant Rana

Categories: Microsoft Dynamics CRM, SqlServer Reporting Services

Tags: CRM, Microsoft Dynamics CRM

« Older Newer »

6 Responses to “How to – Find shared records in CRM”

1. it really helped me thanks :]

   LikeLike

   By PatrickM on December 29, 2010 at 7:44 PM

2. can i do that via web services?
   Is there any way to do that in “advanced find”?
   thank you

   LikeLike

   By gabriele on September 26, 2011 at 3:11 PM

   1. principalObjectAccess is not available in advance find so can’t
      try this and create custom xml

      http://fxb.xrmtoolbox.com/

      LikeLike

      By Shahzeb Dogar on April 19, 2016 at 4:16 PM

3. How can i retrieve this data from table PrincipalOBjectAccess on javascript? using oData json? could you help me ?

   LikeLike

   By Renan Albuquerque on May 22, 2013 at 6:05 PM

   1. using odata json is not possible i guess try to create your desired fetchxml and use it.

      http://fxb.xrmtoolbox.com/

      LikeLike

      By Shahzeb Dogar on April 19, 2016 at 4:13 PM

4. It is a pity, that now I can not express – it is very occupied. I will be released – I will necessarily express the opinion on this question.

   LikeLike

   By ko4c4yashin on November 21, 2021 at 1:21 PM

Leave a Reply

Mobile Site | Full Site

Get a free blog at WordPress.com Theme: WordPress Mobile Edition by Alex King.