Reports Permitted
The following View shows the Reports which have been granted permissions per Security Role.
Difficulty: Advanced
Purpose: Uses the Role Reports system tables to display Reports with permissions.
Page: Security Roles
Field List
The following fields use a STUFF function to return lists of Tools for each Security Role:
dp_Roles.Role_Name,STUFF((SELECT ', ' + R.Report_Name FROM dp_Role_Reports RR JOIN dp_Reports R ON RR.Report_ID = R.Report_ID WHERE RR.Role_ID = dp_Roles.Role_ID FOR XML PATH('')),1,2,'') AS [Reports]View Clause
The following clause limits the list of Security Roles to those with Report permissions. Security Roles without any permissions will drop out of the View:
EXISTS(SELECT 1 FROM dp_Role_Reports RR JOIN dp_Reports R ON RR.Report_ID = R.Report_ID WHERE RR.Role_ID = dp_Roles.Role_ID)Order By
It makes sense to order these by name:
dp_Roles.Role_NameSimilar Views
Techniques
- STUFF SQL Function
- EXISTS SQL Function