Finding CRM Accounts that have been shared

On December 4, 2006, in Customization, Dynamics CRM, by Mitch Milam

Morning Folks,

A couple of months ago, we started a conversation on this blog regarding the identification of CRM Accounts that have been shared.  Satoshi Kawamura of Collins Computing came up with a SQL script that will solve the solution, and with his permission, I am posting it here.

Note: this needs to be run via SQL Query Analyzer on the <Organization Name>_MSCRM database.

select
	ChangedOn 'Shared on',
	name 'Account Name',
	fullname 'Share Name',
	owneridname 'Account Owner', 'User' 'Type'
from
	PrincipalObjectAccess,
	FilteredAccount,
	FilteredSystemUser
where
	PrincipalObjectAccess.ObjectTypeCode = 1
	  and PrincipalObjectAccess.ObjectId = FilteredAccount.accountid
	  and PrincipalObjectAccess.PrincipalId = FilteredSystemUser.systemuserid
union
select
	ChangedOn 'Shared On',
	FilteredAccount.name 'Share Name',
	FilteredTeam.name,
	owneridname 'Account Owner', 'Team' 'Type'
from
	PrincipalObjectAccess,
	FilteredAccount,
	FilteredTeam
where
	PrincipalObjectAccess.ObjectTypeCode = 1
	  and PrincipalObjectAccess.ObjectId = FilteredAccount.accountid
	  and PrincipalObjectAccess.PrincipalId = FilteredTeam.teamid
order by name

 This will product a report similar to the following:

I should also note that this will only produce a list of shared Accounts. Should you wish Contacts, you will need to replace each instance of "FilteredAccount" with "FilteredContact" and change "ObjectTypeCode = 1" to "ObjectTypeCode = 2".

Great work Steve, and thanks for sharing.

 

2 Responses to Finding CRM Accounts that have been shared

  1. ayazahmad says:

    Unfortunately the article is related to the old version MSCRM 1.2.
    Now I was assigned the mammoth task of creating a report to display
    all the shared accounts by a Team. I searched the technical articles
    to get a starting point for the solution but in vain. All the
    articles and code snippets consisted of Sql based solutions etc. So
    finally I had to come up with my own intuition once again, ļ and came
    up with the following function that I would like to share with all
    you developers of MSCRM 3.0. Love to get feedback on this and if some
    one has alternate method, do let me know.

    void ListSharedObjectByTeams(Guid TeamId)
    {
    try
    {
    CrmService service = new CrmService();
    service.Credentials =
    System.Net.CredentialCache.DefaultCredentials;

    // Create the ColumnSet indicating
    the fields to be retrieved
    ColumnSet cols = new ColumnSet();

    // Sets the ColumnSet's Properties
    cols.Attributes = new string []
    {"name", "accountid"};

    // Create the QueryExpression Object
    QueryExpression query = new
    QueryExpression();

    // Set the QueryExpression Object's
    Properties
    query.EntityName =
    EntityName.account.ToString();
    query.ColumnSet = cols;

    // Retrieve the Contacts
    BusinessEntityCollection bus =
    service.RetrieveMultiple(query);
    for (int i = 0;
    i

  2. [...] I just love the CRM community.  Ayaz Ahmad of Ovex Technologies Pvt. Ltd. in Pakistan posted a comment on the previous article about finding Shared CRM Accounts and included a C# function written to perform a similar function to the SQL code in the other article.  [...]

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>