locked
Trying to find records which are shared. RRS feed

  • Question

  • We have a situation where over time many records were unknowingly shared to their previous owner after ownership was changed.  We have now changed the setting in CRM to not share records with the previous owner but would now like to find all the records which have been shared so that we can "un-share" them.

    Is there a query of some kind which will allow me to search for these records?


    Regards, Bill
    Monday, January 10, 2011 11:24 PM

Answers

  • Microsoft CRM Sharing Summary Report will provide you a complete details of all the records at each level.

    Hope this help you.

    Tuesday, January 11, 2011 4:02 AM
  • Ya i am sucessfull with the report.

    If this is the case you can try to check the report from the report manager http://SqlServer/Reports

    Also you can use (copy the query from the dataset by opening the report in business intelligence studio) the report query for your verification. You can directly run in SQL.

    Tuesday, January 11, 2011 4:31 AM

All replies

  • This blog posting should give you a starting point.  As far as I know, you can't build such a query with either Advanced Find or even the SDK API (you can find all principals with which a given entity has been shared, but not the reverse).  The link shows how to do it with a direct SQL query.
    --pogo (pat)
    Monday, January 10, 2011 11:33 PM
  • Thanks for the reply.  The post shows the following as a query to return leads which are shared:

    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’

    I ran it against the org_MSCRM database and it returns no results.  I created a new lead and shared it with a user and inserted the user's domainname into the script with no results returned.  Any other thoughts? 


    Regards, Bill
    Tuesday, January 11, 2011 2:00 AM
  • Did you supply 'DOMAIN\username' as the fsu.domainname?


    --pogo (pat)
    Tuesday, January 11, 2011 2:31 AM
  • Microsoft CRM Sharing Summary Report will provide you a complete details of all the records at each level.

    Hope this help you.

    Tuesday, January 11, 2011 4:02 AM
  • No, just username.  I will try domain\username.


    Regards, Bill
    Tuesday, January 11, 2011 4:22 AM
  • Vinoth,

    I do have that report from codeplex but when I try to run the report, it seems to just error out with a SQL timeout.  I will have another user run it who is a domain admin.  I'm not sure if it's a db permissions issue.

    Have you successfully used the report?

    Bill


    Regards, Bill
    Tuesday, January 11, 2011 4:24 AM
  • Ya i am sucessfull with the report.

    If this is the case you can try to check the report from the report manager http://SqlServer/Reports

    Also you can use (copy the query from the dataset by opening the report in business intelligence studio) the report query for your verification. You can directly run in SQL.

    Tuesday, January 11, 2011 4:31 AM
  • Great suggestion.  I will try that.  Thanks.
    Regards, Bill
    Tuesday, January 11, 2011 2:41 PM