The following View shows the Reports which have been granted permissions per Security Role.

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_Name

Similar Views

• Security Role Pages 
• Tools Permitted
• Field Restrictions

Techniques

• STUFF SQL Function
• EXISTS SQL Function

Keywords: Reports, Noun, User, Permission, Security Role.