CONTACT USBack to MinistryPlatform Site
Your Knowledge Base has moved to the new Help Center.  Check out the release notes for details. And don't forget to update your bookmarks and in-house documentation before May 28.

Page Permissions

 
Counting Your Users

Field List

(SELECT COUNT(*) FROM dp_User_Roles UR WHERE UR.Role_ID = dp_Roles.Role_ID) AS User_Count

Filter 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]

View 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_Name
All 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