Search Wiki:
Resource Page Description
Reporting 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 -
Last edited Aug 7 2009 at 3:50 PM  by DavidJennaway, version 6
mengchew0113 wrote  Aug 5 2009 at 2:26 AM  
Thanks for sharing.
It is a good report to share, but the reporting image was not shown properly.
I think it will be nice to teach how to have SQL permission to select from these tables for newbies.

fredericpenalver wrote  Aug 5 2009 at 8:52 AM  
Hi, indeed, images are not shown properly for inherited roles (in child BU).
It seems that the PrivilegeDepthMask has a 4 byte left shift when it's inherited.
I managed to fix this by adding 4 embedded images to the reports: role16, role32, role64, role128. I could have also modified the sql request.
Otherwise, these are nice reports!

DavidJennaway wrote  Aug 7 2009 at 3:34 PM  
Thanks for the comments about the images for child BUs. I've updated the reports to include these additional images. I've also included reports by user which have the cumulative permissions across all their roles

Page view tracker