SQL query provides CRM 2011 Security Role privilege options in Excel

Have you ever been in a situation where you need an Excel based method of presenting to a client what out of the box Security Roles and permissions are available within Dynamics CRM?  Luckily, this can be accomplished with a SQL query and some pivot table magic. To provide this information in an Excel format, I used the following SQL query to retrieve this data from a CRM 2011 environment.  The same query will work with CRM 2013 as well:

SELECT DISTINCT FilteredRole.name, EntityView.PhysicalName AS [Entity Name], CASE Privilege.AccessRight WHEN 1 THEN 'READ' WHEN 2 THEN 'WRITE' WHEN 4 THEN 'APPEND' WHEN 16 THEN 'APPENDTO' WHEN 32 THEN 'CREATE' WHEN 65536 THEN 'DELETE' WHEN 262144 THEN 'SHARE' WHEN 524288 THEN 'ASSIGN' END AS [Access Level], CASE PrivilegeDepthMask WHEN 1 THEN 'User' WHEN 2 THEN 'Business Unit' WHEN 4 THEN 'Parent: Child Business Unit' WHEN 8 THEN 'Organisation' END AS [Security Level] FROM RolePrivileges INNER JOIN FilteredRole ON RolePrivileges.RoleId = FilteredRole.roleid INNER JOIN code> PrivilegeObjectTypeCodes ON RolePrivileges.PrivilegeId = PrivilegeObjectTypeCodes.PrivilegeId INNER JOIN Privilege ON RolePrivileges.PrivilegeId = Privilege.PrivilegeId INNER JOIN EntityView ON EntityView.ObjectTypeCode = PrivilegeObjectTypeCodes.ObjectTypeCode ORDER BY FilteredRole.name, [Entity Name]

The resulting Excel spreadsheet, after massaging the data with a pivot table, provides a client ready example of what each OOB security role provides for every permission.  Let me know what you think!  If you have any recommendations or improvements, please share that as well!  Thanks. CRMHobbit (Shawn)


Author: crmhobbit

The CRMhobbit (Shawn Tabor) is a Microsoft Business Solutions MVP and a Sr. Solutions Architect. He has experience implementing Dynamics 365 for Customer Engagement across various vertical industries including Financial Services, Manufacturing, Public Sector and Sports & Entertainment. Shawn specializes in the Dynamics 365 for Field Service, Project Service Automation and LinkedIn Sales Navigator solutions. The purpose of his blog is to share his knowledge, experience and thoughts regarding CRM in the hopes of benefiting those in the CRM community. If anything on this blog has helped you, please comment or rate the posts. Thank you!

One thought on “SQL query provides CRM 2011 Security Role privilege options in Excel”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s