MS CRM Security Roles Audit

To fetch all security role entity vise its and security level. Paste below query it can help you to fetch all the information from MS CRM.

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]

Result will be in below specefied format:

	entity name	Access level	security level
Security test 1	PhoneCall	APPEND	Parent: Child Business Unit
Security test 2	PhoneCall	APPENDTO	Parent: Child Business Unit
Security test 3	PhoneCall	CREATE	User
Security test 4	PhoneCall	DELETE	User
Security test 5	PhoneCall	READ	Organisation
Security test 6	PhoneCall	SHARE	User
Security test 7	PhoneCall	WRITE	User

Thanks.