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.
Loading ...
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
[...] 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. [...]