MSDN Archive Home
Help and FAQs
CRM Reports on Role Privileges
All Resource Updates
Change History (all pages)
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 -
Aug 7 2009 at 3:50 PM
, version 6
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.
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!
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
Sign in to add a comment
Reports and SQL by Rol...
Fri Aug 7 2009 at 7:00 AM
More Tags ...
Visual Studio 2005
Visual Studio 2008
Visual Studio 2010
Manage Your Profile
MSDN Flash Newsletter
© 2008 Microsoft Corporation. All rights reserved.