How to Export to Excel and Compare Security Roles
The CRM come already with some kind of report (see image Users Report) that allow us see all the rules applied to each user but don't show what is select on each role. Therefore, here is a Select statement that you can run on your CRM DB to get roles and access levels.
SELECT
FilteredRole.name,
EntityView.PhysicalName,
Privilege.Name,
AccessLevel =
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,
SecurityLevel =
CASE PrivilegeDepthMask
WHEN 1 THEN 'User'
WHEN 2 THEN 'Business Unit'
WHEN 4 THEN 'Parent: Child Business Unit'
WHEN 8 THEN 'Organisation'
END
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
WHERE FilteredRole.roletemplateid is null
ORDER BY FilteredRole.name, EntityView.PhysicalName, Privilege.AccessRight
PS: This only works for custom roles, if you want to retrieve a full set please remove the where clause is.
Copy & Paste the SQL Table to Excel
Create a Pivot Table on Excel to see what Options are assign to each Role
Users Report
Hope this is useful because it uses SQL and it is not supported but again I guess there is no harm in reading from tables and views.
Thanks for Sharing such a useful information All in one pack with full on Information
ReplyDeleteMB-210: Microsoft Dynamics 365 Sales
Thank You for sharing such a wonderful blog with us. It is very useful to me. To get more information about MB-220: Microsoft Dynamics 365 Marketing
ReplyDelete