Report showing pages that a role or user is applied to

Brandon White asked on June 20, 2018 19:23

I'm trying to create a report that would allow someone to select a role and return the pages that have that role applied directly and not inherited. Would also be helpful to do the same by selecting a user. This would allow us to see what main nodes we've specifically applied a role or user to in case we want to clone a role and apply slightly different permissions.

Right now I just have this SQL statement: SELECT NodeID, NodeAliasPath, DocumentName, RoleDisplayName FROM View_CMS_UserRole_Joined JOIN View_CMS_UserDocuments ON View_CMS_UserRole_Joined.RoleID = View_CMS_UserRole_Joined.RoleID where RoleDisplayName = 'Education Super User'

I'm not sure how to take into account if a role is inherited or not on a page, or if these are the best views to use.

user reporting roles

Correct Answer

Brenden Kehren answered on June 21, 2018 16:22

For this I beleive you'll need to bring in the CMS_ACL and CMS_ACLItem tables to check the access control list for inheritance and get the actual item(s)/page(s) that way.

0 votesVote for this answer Unmark Correct answer

Recent Answers

Brandon White answered on June 22, 2018 16:24

Thanks Brenden, I think I got it:

SELECT * FROM CMS_ACL Join CMS_ACLItem on CMS_ACL.ACLID = CMS_ACLItem.ACLID Join CMS_role on CMS_ACLItem.RoleID = CMS_Role.RoleID Join CMS_Tree on CMS_Tree.NodeACLID = CMS_ACL.ACLID where ACLInheritedACLs = '' and NodeIsACLOwner = 1 and RoleDisplayName like 'Giving Editor%'

1 votesVote for this answer Mark as a Correct answer