MS-CRM - Query to list the Access Rights of the Role's on all the Entities

Recently, one of the Business Analyst associated with the CRM project wanted to know the access rights for all the roles across all the entities using T-SQL? The MS-CRM (4.0) database for which end user intended to fetch this information did not have any organization created on the top of it. Thus end users were not able to know what roles has what permission and on what entity...

I thought it would be good exercise to write a query to fetch this information. So I started writing one and at the end had following query ready with me.. It fetches required information for the roles which are associated with the Active users only. This means the roles which are not in use (or part of Inactive user) would not feature in the output of the query

Database Objects used in the query are listed below

• PrivilegeObjectTypeCodes - Contains the association between PrivilegeId and ObjectTypeCode of the entity
• PrivilegeBase - Contains the information about privileges such as Name, GUID etc..
• RolePrivileges -  Association between Roles and Privileges is defined here 
• RoleBase - Contains the information about Roles such as Name, GUID etc..
• EntityView - Has the information about Entities
• SystemUserRoles -  Association between Users and Roles
• SystemUser - Information about CRM users

Following link played pivotal role in understanding the data from the database objects

http://blogs.msdn.com/b/crm/archive/2009/08/04/viewing-all-crm-privileges-including-hidden-privileges.aspx

Query as follows


;WITH DesiredRoles AS (--- Roles belonging to active users will only be considered due to this condition
   SELECT
       DISTINCT FRole.name AS RoleAssigned
       ,FRole.RoleId
   FROM SystemUser FUser
   INNER JOIN SystemUserRoles FUserRole
   ON FUser.systemuserid = FUserRole.systemuserid
   INNER  JOIN Role FRole
   ON FRole.roleid = FUserRole.roleidWHERE FUser.isdisabled = 0
),AccessRights AS(
   SELECT
   PO.[ObjectTypeCode]
   ,EM.Name AS Entityname
   ,CASE Pri.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 AccessType
   ,RP.PrivilegeDepthMask AS AccessLevel
   ,RO.Name
   FROM [dbo].[PrivilegeObjectTypeCodes] PO
   INNER JOIN [dbo].PrivilegeBase Pri
   ON Po.PrivilegeId = Pri.PrivilegeId
   INNER JOIN [dbo].RolePrivileges RP
   ON PRI.PrivilegeId = RP.PrivilegeId
   INNER JOIN [dbo].RoleBase RO
   ON RO.RoleId = RP.RoleId
   INNER JOIN [dbo].EntityView EM
   ON PO.ObjectTypeCode = EM.ObjectTypeCode
   INNER JOIN DesiredRoles DR
   ON DR.RoleId = RO.RoleId
)SELECT
   Name AS RoleName,
   Entityname,
   CASE [Create]
     WHEN 1 THEN 'User'
     WHEN 2 THEN 'Business Unit'
     WHEN 4 THEN 'Parent: Child'
     WHEN 8 THEN 'Organisation'
   END AS [Create],
   CASE [Read]
     WHEN 1 THEN 'User'
     WHEN 2 THEN 'Business Unit'
     WHEN 4 THEN 'Parent: Child'
     WHEN 8 THEN 'Organisation'
   END AS [Read],
   CASE [Write]
     WHEN 1 THEN 'User'
     WHEN 2 THEN 'Business Unit'
     WHEN 4 THEN 'Parent: Child'
     WHEN 8 THEN 'Organisation'
   END AS [Write],
   CASE [Delete]
     WHEN 1 THEN 'User'
     WHEN 2 THEN 'Business Unit'
     WHEN 4 THEN 'Parent: Child'
     WHEN 8 THEN 'Organisation'
   END AS [Delete],
   CASE [Append]
     WHEN 1 THEN 'User'
     WHEN 2 THEN 'Business Unit'
     WHEN 4 THEN 'Parent: Child'
     WHEN 8 THEN 'Organisation'
   END AS [Append],
   CASE [AppendTo]
     WHEN 1 THEN 'User'
     WHEN 2 THEN 'Business Unit'
     WHEN 4 THEN 'Parent: Child'
     WHEN 8 THEN 'Organisation'
   END AS [AppendTo],
   CASE [Assign]
     WHEN 1 THEN 'User'
     WHEN 2 THEN 'Business Unit'
     WHEN 4 THEN 'Parent: Child'
     WHEN 8 THEN 'Organisation'
   END AS [Assign],
   CASE [Share]
     WHEN 1 THEN 'User'
     WHEN 2 THEN 'Business Unit'
     WHEN 4 THEN 'Parent: Child'
     WHEN 8 THEN 'Organisation'
   END AS [Share]
FROM AccessRights
PIVOT
   (
       SUM(AccessLevel)
       FOR AccessType IN ([Read],[Write],[Append],[AppendTo],[Create],[Delete],[Share],[Assign])
   )AS Pvt
ORDER BY RoleName,Entityname