Answered by:
CRM Security

Question
-
The query below gives me all the data I need with the exception of the collection each privilege belongs to(Core Records, marketing, Sales, Service etc.) and which Entity it belongs to(Account, Activity, Announcement, Application File, Connection etc.)
Is there a table where this mapping is stored. I would insert images to be more illustrative but I can't because my account isn't verified. Thanks!
select A.Name as BusinessUnitName, B.Name as RoleName, D.Name as PrivilegeName, case when PrivilegeDepthMask = 1 then 'User' else case when PrivilegeDepthMask = 2 then 'BusinessUnit' else case when PrivilegeDepthMask = 4 then 'ParentChild' else case when PrivilegeDepthMask = 8 then 'Organization' end end end end as Depth, case when AccessRight = '1' then 'Read' else case when AccessRight = '2' then 'Write' else case when AccessRight = '4' then 'Append' else case when AccessRight = '16' then 'AppendTo' else case when AccessRight = '32' then 'Create' else case when AccessRight = '65336' then 'Delete' else case when AccessRight = '262144' then 'Share' else case when AccessRight = '524288' then 'Assign' else 'Delete' end end end end end end end end as AccessRight from [DATABASE].[dbo].[BusinessUnit] A inner join [DATABASE].[dbo].[Role] B on A.BusinessUnitId = B.BusinessUnitId inner join [DATABASE].[dbo].[RolePrivileges] C on b.RoleId = c.RoleId inner join [DATABASE].[dbo].[Privilege] D on C.PrivilegeId = D.PrivilegeId where B.Name = 'Activity Feeds' order by D.Name
Tuesday, November 17, 2015 6:28 PM
Answers
-
Unfortunately not. That entity grouping is not stored anywhere in the CRM metadata; as far as I know it's hard-coded into CRM
Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk
- Marked as answer by Bo Dynamics GP Wednesday, November 18, 2015 3:29 PM
Wednesday, November 18, 2015 3:21 PMModerator
All replies
-
You need to join to the PrivilegeObjectTypeCodes table, and then to the EntityAsIfPublishedLogicalView view
select e.Name as EntityName, A.Name as BusinessUnitName, B.Name as RoleName, D.Name as PrivilegeName, case when PrivilegeDepthMask = 1 then 'User' else case when PrivilegeDepthMask = 2 then 'BusinessUnit' else case when PrivilegeDepthMask = 4 then 'ParentChild' else case when PrivilegeDepthMask = 8 then 'Organization' end end end end as Depth, case when AccessRight = '1' then 'Read' else case when AccessRight = '2' then 'Write' else case when AccessRight = '4' then 'Append' else case when AccessRight = '16' then 'AppendTo' else case when AccessRight = '32' then 'Create' else case when AccessRight = '65336' then 'Delete' else case when AccessRight = '262144' then 'Share' else case when AccessRight = '524288' then 'Assign' else 'Delete' end end end end end end end end as AccessRight from [BusinessUnit] A inner join [Role] B on A.BusinessUnitId = B.BusinessUnitId inner join [RolePrivileges] C on b.RoleId = c.RoleId inner join [Privilege] D on C.PrivilegeId = D.PrivilegeId join PrivilegeObjectTypeCodes poc on D.PrivilegeId = poc.PrivilegeId join EntityAsIfPublishedLogicalView e on poc.ObjectTypeCode = e.ObjectTypeCode where B.Name = 'Activity Feeds' order by D.Name
Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk
Wednesday, November 18, 2015 11:00 AMModerator -
Thank You!
By joining to those two tables I can now get to the Entity as displayed on the CRM Security Reports. Now, is there a field in the PrivilegeObjectTypeCodes Table or in another table that groups the Entities into Collections as defined on the CRM Security Reports?
ie. Core Records, Marketing, Sales, Service, Business Management, Service Management, Customization, Custom Entities
Wednesday, November 18, 2015 3:16 PM -
Unfortunately not. That entity grouping is not stored anywhere in the CRM metadata; as far as I know it's hard-coded into CRM
Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk
- Marked as answer by Bo Dynamics GP Wednesday, November 18, 2015 3:29 PM
Wednesday, November 18, 2015 3:21 PMModerator -
Thanks! Anybody else have any thoughts on this?
Wednesday, November 18, 2015 3:29 PM