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.