Resource Page DescriptionReporting Services reports against Microsoft CRM data, displaying all role privileges, including hidden privileges
This resource contains SQL reports and the associated SQL to display privileges by security role and/or business unit. There are 2 reports by role: MiscellaneousPrivilegesByRole and EntityPrivilegesByRole which display different priviliges, and are laid out differently. MiscellaneousPrivilegesByRole displays as a matrix to easily compare privileges across roles, whereas EntityPrivilegesByRole outputs result one role after another, with a layout that mimics the CRM UI. There are also 2 reports by user: MiscellaneousPrivilegesByUser and EntityPrivilegesByUser which display the cumulative permissions across all roles they're a member of (these 2 reports were added on 7 Aug 09).
The role reports use multi-select parameters for the role and business unit, and the user reports have a multi-select parameter for the user. Reporting Services parameters are used, rather than CRM pre-filtering, as the Role and BusinessUnit entities aren't available for pre-filtering in CRM.
The reports can be directly deployed via CRM, as they have embedded data sources. If you want to deploy or develop them outside of CRM this is perfectly possible - you just need to change the DataSource information.
Note that the reports access some CRM tables directly (RolePrivileges, PrivilegeObjectTypeCodes and MetadataSchema.Entity) as there is no associated filtered view for these tables. Hence the reports will throw an error unless you have SQL permission to select from these tables. I've added a download 'Grant Select Permission SQL' with the SQL to grant these permissions; alternatively this can be done in SQL Management Studio (see SQL Books Online for instructions).
For more information about hidden privileges, and the results from these reports, see the post on the Microsoft Dynamics CRM Team Blog -
http://blogs.msdn.com/crm/archive/2009/08/04/viewing-all-crm-privileges-including-hidden-privileges.aspx