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
