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.

Fourteen

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]

Dozier

would role = dbo.rolebase potentially ? – Fourteen

Role is a view in the database, it has a bit more information that just the RoleBase table. RoleBase is the underlying base table. Role = rolebase + more info. – Dozier

nearly there where does MetadataSchema come from ? cant find it in views or tables, could it be called something else ? – Fourteen

That is just another schema in the CRM database - like the dbo schema. That schema has tables like MetadataSchema.Entity and MetadataSchema.Attributes - which has information about all the entities and attributes respectively. If you are looking for it in SQL Server Management Studio - it will be towards the end of the list of tables. – Dozier

MetadataSchema is a schema not a table. blog.sqlauthority.com/2009/09/07/… – Dozier

The query I posted will run without any modifications - if it does not, then you have bigger problems. – Dozier

Answer 2

Just in case anyone finds themselves in my position. I took Donal's answer and developed it a bit further. The first query will get you the user acces roles specific to entities, formatted similarily as in the UI. However it does not print the mics rights that are displayed at the bottom of the page in the UI, as those aren't linked to entities. Use the second query to get those. I added a column displaying the current environment, this allows a quick comparison between different instances of the CRM.

User Role Rights concerning entities:

With groupRights AS (

SELECT  DISTINCT
        [MSCRM_CONFIG].[dbo].[Organization].friendlyname AS Environment, 
        COALESCE(e.OriginalLocalizedName, e.Name) AS [Entity],
        r.Name as [Role],
        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]
        ,(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
INNER   JOIN dbo.RoleBase 
        ON dbo.RoleBase.RoleId = rp.RoleId
INNER   JOIN [MSCRM_CONFIG].[dbo].[Organization] 
        ON dbo.RoleBase.OrganizationId = [MSCRM_CONFIG].[dbo].[Organization].Id
)
SELECT * FROM groupRights

PIVOT
(
    avg(PrivilegeDepthMask)
    FOR Privilege 
    IN ([Create], [Read], [Write], [Delete], [Append], [AppendTo], [Assign], [Share], [Error])
) p

ORDER   BY [Environment], [Entity], [Role]--, [PrivilegeLevel]

And the MISC rights:

SELECT  DISTINCT
        [MSCRM_CONFIG].[dbo].[Organization].friendlyname AS Environment, 
        r.Name as [Role],
        p.name as [Privilege Name],

        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]
        ,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
Left outer JOIN Privilege AS p 
        ON rp.PrivilegeId = p.PrivilegeId
Left outer JOIN PrivilegeObjectTypeCodes AS potc 
        ON potc.PrivilegeId = p.PrivilegeId
Left outer JOIN MetadataSchema.Entity AS e
        ON e.ObjectTypeCode = potc.ObjectTypeCode
INNER   JOIN dbo.RoleBase 
        ON dbo.RoleBase.RoleId = rp.RoleId
INNER   JOIN [MSCRM_CONFIG].[dbo].[Organization] 
        ON dbo.RoleBase.OrganizationId = [MSCRM_CONFIG].[dbo].[Organization].Id

where e.OriginalLocalizedName is null
and e.Name is null

Tameka