Useful CRM Tips

Useful CRM Tips...

Wednesday, February 26, 2014

SQL Query to Retrieve All Security Roles Have Which are all Permissions on which Entities

The following SQL statement will retrieve a list of security roles and the level of access each security role has in regards to entities. this will be useful for scenarios when you want to see who has "Delete" permissions on "Contacts" for instance.
Simply run the select query against the CRM database
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
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]