Dynamics CRM 2011 Security Role SQL query

Question

I'm new to Dynamics CRM and I'm trying to export security roles from the SQL back end of CRM to create a report of each security roles read, write, create etc permissions. Has anyone done this before as the CRM tables are quite vague.

Answer 1

The following will list every Security Role, the entities it relates to - also the privileges and access levels:

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]