The following View shows the Tools 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 ', ' + T.Tool_Name
 FROM dp_Role_Tools RT JOIN dp_Tools T ON RT.Tool_ID = T.Tool_ID
 WHERE RT.Role_ID = dp_Roles.Role_ID
 FOR XML PATH('')),1,2,'') AS [Tools]

View Clause

The following clause limits the list of Security Roles to those with Tool permissions. Security Roles without any permissions will drop out of the View:

EXISTS (SELECT 1
 FROM dp_Role_Tools RT JOIN dp_Tools T ON RT.Tool_ID = T.Tool_ID
 WHERE RT.Role_ID = dp_Roles.Role_ID)

Order By

It makes sense to order these by name:

Role_Name

Similar Views

• Reports Permitted
• Security Role Pages 
• Field Restrictions

Techniques

• STUFF SQL Function
• EXISTS SQL Function

Keywords: Tools, Noun, User, Permission, Security Role