locked
CRM Security RRS feed

  • 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 PM
    Moderator

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 AM
    Moderator
  • 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 PM
    Moderator
  • Thanks!  Anybody else have any thoughts on this?

    Wednesday, November 18, 2015 3:29 PM