Dynamics CRM 365 查询安全角色的具体权限明细

SELECT  DISTINCT
        r.Name 安全角色名称
        ,COALESCE(e.OriginalLocalizedName, e.Name) AS [EntityName] 
        ,CASE p.AccessRight
             WHEN 32     THEN 'Create-创建' /* or hex value 0x20*/
             WHEN 1      THEN 'Read-读'
             WHEN 2      THEN 'Write-写'
             WHEN 65536  THEN 'Delete-删除' /* or hex value 0x10000*/
             WHEN 4      THEN 'Append-追加'
             WHEN 16     THEN 'AppendTo-追加到'
             WHEN 524288 THEN 'Assign-分派' /* or hex value 0x80000*/
             WHEN 262144 THEN 'Share-共享' /* or hex value 0x40000*/
             ELSE 'None'
        END AS [Privilege]
        ,CASE (rp.PrivilegeDepthMask % 0x0F)
             WHEN 1 THEN 'User (Basic)-个人'
             WHEN 2 THEN 'Business Unit (Local)-业务部门'
             WHEN 4 THEN 'Parental (Deep)-上下级'
             WHEN 8 THEN 'Organization (Global)-组织'
             ELSE 'Unknown'
        END AS [PrivilegeLevel]
        ,(rp.PrivilegeDepthMask % 0x0F) as [PrivilegeDepthMask]
        ,CASE WHEN e.IsCustomEntity = 1 THEN 'Yes' ELSE 'No' END AS [IsCustomEntity]
FROM    Role AS r 
INNER   JOIN RolePrivileges AS rp 
        ON r.RoleId = rp.RoleId
INNER   JOIN Privilege AS p 
        ON rp.PrivilegeId = p.PrivilegeId
INNER   JOIN PrivilegeObjectTypeCodes AS potc 
        ON potc.PrivilegeId = p.PrivilegeId
INNER   JOIN MetadataSchema.Entity AS e
        ON e.ObjectTypeCode = potc.ObjectTypeCode
ORDER   BY r.Name, [EntityName]