Remove From My Forums

Generate a report on permissions

Question
0

Sign in to vote

Hi,

can anyone help?

I am using Dynamics CRM 2011 and have a number of the contacts and accounts shared.

I want to produce a report for admin staff which will output a list of all users which have access to contacts (either through sharing or because they own the contact) and don't have access to the associated account.

I can see how to do this programmatically (I think it's through the RetrieveSharedPrincipalsAndAccessResponse class), but I can't work out what the equivalent database views are to allow me to put this information into a report.

Any help would be appreciated.

Thanks

Friday, July 15, 2011 4:40 PM

Answers

It is CRM 4.0, but the report in the attached should get you started (underlying table structures won't have changed a great deal).

http://sharingsummary.codeplex.com/releases/view/33106#DownloadId=83838

I ran the underlying query directly against a CRM 2011 DB and it returned results, but only after adding a few "top 1" statements:

SELECT   PrincipalObjectAccess.PrincipalObjectAccessId, PrincipalObjectAccess.ObjectId, PrincipalObjectAccess.ObjectTypeCode,
             (SELECT  top 1 PhysicalName
              FROM     MetadataSchema.Entity
              WHERE   (ObjectTypeCode = PrincipalObjectAccess.ObjectTypeCode)) AS ObjectTypeCodeName, CASE 
     WHEN PrincipalObjectAccess.ObjectTypeCode =  1 THEN (SELECT name FROM FilteredAccount WHERE objectid = accountid)
     WHEN PrincipalObjectAccess.ObjectTypeCode =  2 THEN (SELECT fullname FROM FilteredContact WHERE objectid = contactid)
     WHEN PrincipalObjectAccess.ObjectTypeCode =  3 THEN (SELECT name FROM FilteredOpportunity WHERE objectid = opportunityid)
     WHEN PrincipalObjectAccess.ObjectTypeCode =  4 THEN (SELECT fullname FROM FilteredLead WHERE objectid = leadid)
     WHEN PrincipalObjectAccess.ObjectTypeCode =  5 THEN (SELECT subject FROM FilteredAnnotation WHERE PrincipalObjectAccess.objectid = annotationid)
     WHEN PrincipalObjectAccess.ObjectTypeCode = 112 THEN (SELECT title FROM FilteredIncident WHERE objectid = incidentid)
     WHEN PrincipalObjectAccess.ObjectTypeCode = 1084 THEN (SELECT name FROM FilteredQuote WHERE objectid = quoteid)
     WHEN PrincipalObjectAccess.ObjectTypeCode = 1088 THEN (SELECT name FROM FilteredSalesorder WHERE objectid = salesorderid)
     WHEN PrincipalObjectAccess.ObjectTypeCode = 1090 THEN (SELECT name FROM FilteredInvoice WHERE objectid = invoiceid)
     WHEN PrincipalObjectAccess.ObjectTypeCode = 1010 THEN (SELECT title FROM FilteredContract WHERE objectid = contractid)
     WHEN PrincipalObjectAccess.ObjectTypeCode = 1011 THEN (SELECT title FROM FilteredContractdetail WHERE objectid = contractdetailid)
     WHEN PrincipalObjectAccess.ObjectTypeCode > 4200 OR PrincipalObjectAccess.ObjectTypeCode <= 4215 THEN (SELECT top 1 subject FROM FilteredActivityPointer WHERE objectid = activityid)
     ELSE 'N/A' END AS ObjectIdName, CASE 
     WHEN PrincipalObjectAccess.ObjectTypeCode =  1 THEN (SELECT owneridname FROM FilteredAccount WHERE objectid = accountid)
     WHEN PrincipalObjectAccess.ObjectTypeCode =  2 THEN (SELECT owneridname FROM FilteredContact WHERE objectid = contactid)
     WHEN PrincipalObjectAccess.ObjectTypeCode =  3 THEN (SELECT owneridname FROM FilteredOpportunity WHERE objectid = opportunityid)
     WHEN PrincipalObjectAccess.ObjectTypeCode =  4 THEN (SELECT owneridname FROM FilteredLead WHERE objectid = leadid)
     WHEN PrincipalObjectAccess.ObjectTypeCode =  5 THEN (SELECT owneridname FROM FilteredAnnotation WHERE PrincipalObjectAccess.objectid = annotationid)
     WHEN PrincipalObjectAccess.ObjectTypeCode = 112 THEN (SELECT owneridname FROM FilteredIncident WHERE objectid = incidentid)
     WHEN PrincipalObjectAccess.ObjectTypeCode = 1084 THEN (SELECT owneridname FROM FilteredQuote WHERE objectid = quoteid)
     WHEN PrincipalObjectAccess.ObjectTypeCode = 1088 THEN (SELECT owneridname FROM FilteredSalesorder WHERE objectid = salesorderid)
     WHEN PrincipalObjectAccess.ObjectTypeCode = 1090 THEN (SELECT owneridname FROM FilteredInvoice WHERE objectid = invoiceid)
     WHEN PrincipalObjectAccess.ObjectTypeCode = 1010 THEN (SELECT owneridname FROM FilteredContract WHERE objectid = contractid)
     WHEN PrincipalObjectAccess.ObjectTypeCode > 4200 OR PrincipalObjectAccess.ObjectTypeCode <= 4215 THEN (SELECT top 1 owneridname FROM FilteredActivityPointer WHERE objectid = activityid)
     ELSE 'N/A' END AS ObjectIdOwnerName, CASE 
     WHEN PrincipalObjectAccess.ObjectTypeCode =  1 THEN (SELECT ownerid FROM FilteredAccount WHERE objectid = accountid)
     WHEN PrincipalObjectAccess.ObjectTypeCode =  2 THEN (SELECT ownerid FROM FilteredContact WHERE objectid = contactid)
     WHEN PrincipalObjectAccess.ObjectTypeCode =  3 THEN (SELECT ownerid FROM FilteredOpportunity WHERE objectid = opportunityid)
     WHEN PrincipalObjectAccess.ObjectTypeCode =  4 THEN (SELECT ownerid FROM FilteredLead WHERE objectid = leadid)
     WHEN PrincipalObjectAccess.ObjectTypeCode =  5 THEN (SELECT ownerid FROM FilteredAnnotation WHERE PrincipalObjectAccess.objectid = annotationid)
     WHEN PrincipalObjectAccess.ObjectTypeCode = 112 THEN (SELECT ownerid FROM FilteredIncident WHERE objectid = incidentid)
     WHEN PrincipalObjectAccess.ObjectTypeCode = 1084 THEN (SELECT ownerid FROM FilteredQuote WHERE objectid = quoteid)
     WHEN PrincipalObjectAccess.ObjectTypeCode = 1088 THEN (SELECT ownerid FROM FilteredSalesorder WHERE objectid = salesorderid)
     WHEN PrincipalObjectAccess.ObjectTypeCode = 1090 THEN (SELECT ownerid FROM FilteredInvoice WHERE objectid = invoiceid)
     WHEN PrincipalObjectAccess.ObjectTypeCode = 1010 THEN (SELECT ownerid FROM FilteredContract WHERE objectid = contractid)
     WHEN PrincipalObjectAccess.ObjectTypeCode > 4200 OR PrincipalObjectAccess.ObjectTypeCode <= 4215 THEN (SELECT top 1 ownerid FROM FilteredActivityPointer WHERE objectid = activityid)
     ELSE 'N/A' END AS ObjectIdOwnerId, PrincipalObjectAccess.PrincipalTypeCode, 
           PrincipalObjectAccess.AccessRightsMask, PrincipalObjectAccess.ChangedOn, PrincipalObjectAccess.VersionNumber, 
           PrincipalObjectAccess.InheritedAccessRightsMask, FilteredSystemUser.FullName, FilteredSystemUser.DomainName, FilteredSystemUser.SystemUserId
FROM     PrincipalObjectAccess INNER JOIN
           FilteredSystemUser ON PrincipalObjectAccess.PrincipalId = FilteredSystemUser.SystemUserId

--pogo (pat) @ pogo69.wordpress.com

Marked as answer by Ash 1 Monday, July 18, 2011 8:44 AM

Friday, July 15, 2011 7:46 PM

All replies