Wednesday, 20 August 2014

SQL Query to get all the Privileges of a all roles of MS-CRM 2013 system.

Today i came across a requirement from my customer to get all the Privileges of a particular user.Below is the query to get all privileges for all roles.

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]


Thanks to CRM Hobbit's blog for his blog.

I need to modify this to further suit my requirement.

Cheers,
Rajesh