Tools Permitted
The following View shows the Tools which have been granted permissions per Security Role.
Difficulty: Advanced
Purpose: Uses the Role Tools system tables to display Tools 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 ', ' + 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_NameSimilar Views
Techniques
- STUFF SQL Function
- EXISTS SQL Function