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)

Advertisements

Author: crmhobbit

The CRMhobbit (Shawn Tabor) is a CRM Architect and a licensed securities representative. He has 12+ years experience in the Financial Services industry and 10+ years working with Microsoft Dynamics CRM and other CRM 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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s