Page Permissions
Counting Your Users
Field List
(SELECT COUNT(*) FROM dp_User_Roles UR WHERE UR.Role_ID = dp_Roles.Role_ID) AS User_CountFilter Clause
EXISTS(SELECT * FROM dp_User_Roles UR WHERE UR.Role_ID = dp_Roles.Role_ID)All Pages Which Have Been Granted Permissions
The following View shows all Pages which have been granted permissions:
Difficulty: Advanced
Purpose: Uses the Role Pages system tables to display pages with permissions.
Page: Security Roles
Field List
The following fields use a STUFF function to return lists of Pages for each permission level:
dp_Roles.Role_Name,STUFF((SELECT ', ' + P.Display_Name FROM dp_Role_Pages RP JOIN dp_Pages P ON RP.Page_ID = P.Page_ID WHERE RP.Role_ID = dp_Roles.Role_ID AND RP.Access_Level = 3 FOR XML PATH('')),1,2,'') AS [Full],STUFF((SELECT ', ' + P.Display_Name FROM dp_Role_Pages RP JOIN dp_Pages P ON RP.Page_ID = P.Page_ID WHERE RP.Role_ID = dp_Roles.Role_ID AND RP.Access_Level = 2 FOR XML PATH('')),1,2,'') AS [Mass],STUFF((SELECT ', ' + P.Display_Name FROM dp_Role_Pages RP JOIN dp_Pages P ON RP.Page_ID = P.Page_ID WHERE RP.Role_ID = dp_Roles.Role_ID AND RP.Access_Level = 1 FOR XML PATH('')),1,2,'') AS [Edit],STUFF((SELECT ', ' + P.Display_Name FROM dp_Role_Pages RP JOIN dp_Pages P ON RP.Page_ID = P.Page_ID WHERE RP.Role_ID = dp_Roles.Role_ID AND RP.Access_Level = 0 FOR XML PATH('')),1,2,'') AS [Read],STUFF((SELECT ', ' + P.Display_Name FROM dp_Role_Pages RP JOIN dp_Pages P ON RP.Page_ID = P.Page_ID WHERE RP.Role_ID = dp_Roles.Role_ID AND RP.Quick_Add = 1 FOR XML PATH('')),1,2,'') AS [Quick],STUFF((SELECT ', ' + P.Display_Name FROM dp_Role_Pages RP JOIN dp_Pages P ON RP.Page_ID = P.Page_ID WHERE RP.Role_ID = dp_Roles.Role_ID AND RP.Data_Exporter = 1 FOR XML PATH('')),1,2,'') AS [Export],STUFF((SELECT ', ' + P.Display_Name FROM dp_Role_Pages RP JOIN dp_Pages P ON RP.Page_ID = P.Page_ID WHERE RP.Role_ID = dp_Roles.Role_ID AND RP.File_Attacher = 1 FOR XML PATH('')),1,2,'') AS [Attach],STUFF((SELECT ', ' + P.Display_Name FROM dp_Role_Pages RP JOIN dp_Pages P ON RP.Page_ID = P.Page_ID WHERE RP.Role_ID = dp_Roles.Role_ID AND RP.Secure_Records = 1 FOR XML PATH('')),1,2,'') AS [Secure]CopiedCopiedView Clause
The following clause limits the list of Security Roles to those with permissions. Security Roles without any permissions will drop out of the View:
EXISTS (SELECT 1 FROM dp_Role_Pages RP WHERE RP.Role_ID = dp_Roles.Role_ID)Order By
It makes sense to order these by name:
Role_NameAll Sub-Pages Which Have Been Granted Permissions
To show Sub-Page permissions, the following field list may be used:
dp_Roles.[Role_Name] AS [Role Name],(SELECT STUFF((SELECT ', ' + P.Display_Name + '/' + SP.Display_Name FROM dp_Role_Sub_Pages RSP JOIN dp_Sub_Pages SP ON RSP.Sub_Page_ID = SP.Sub_Page_ID JOIN dp_Pages P ON SP.Page_ID = P.Page_ID WHERE RSP.Role_ID = dp_Roles.Role_ID AND RSP.Access_Level = 3 FOR XML PATH('')),1,2,'')) AS [Full],(SELECT STUFF((SELECT ', ' + P.Display_Name + '/' + SP.Display_Name FROM dp_Role_Sub_Pages RSP JOIN dp_Sub_Pages SP ON RSP.Sub_Page_ID = SP.Sub_Page_ID JOIN dp_Pages P ON SP.Page_ID = P.Page_ID WHERE RSP.Role_ID = dp_Roles.Role_ID AND RSP.Access_Level = 2 FOR XML PATH('')),1,2,'')) AS [Mass],(SELECT STUFF((SELECT ', ' + P.Display_Name + '/' + SP.Display_Name FROM dp_Role_Sub_Pages RSP JOIN dp_Sub_Pages SP ON RSP.Sub_Page_ID = SP.Sub_Page_ID JOIN dp_Pages P ON SP.Page_ID = P.Page_ID WHERE RSP.Role_ID = dp_Roles.Role_ID AND RSP.Access_Level = 1 FOR XML PATH('')),1,2,'')) AS [Edit],(SELECT STUFF((SELECT ', ' + P.Display_Name + '/' + SP.Display_Name FROM dp_Role_Sub_Pages RSP JOIN dp_Sub_Pages SP ON RSP.Sub_Page_ID = SP.Sub_Page_ID JOIN dp_Pages P ON SP.Page_ID = P.Page_ID WHERE RSP.Role_ID = dp_Roles.Role_ID AND RSP.Access_Level = 0 FOR XML PATH('')),1,2,'')) AS [Read]The following filter will only show Security Roles with Sub-Page Permissions:
EXISTS (SELECT 1 FROM dp_Role_Sub_Pages RSP WHERE RSP.Role_ID = dp_Roles.Role_ID)Similar Views
Techniques
- STUFF SQL Function
- EXISTS SQL Function