Recently, we had a scenario, where we need a simple SQL Role which gets read access to bunch of views. While it was easy to create the view and grant permissions. There is no simple sample on the web to get the list of permissions the role has.
After few search and scrubbing the code available on the we, we come with below snippet useful.
--Create a role
CREATE ROLE DB_EXTERNAL_READER
--Grant permission to the role on objects
GRANT SELECT ON vwNpsResponses TO DB_EXTERNAL_READER
GRANT SELECT ON vwNPSResponsesV2 TO DB_EXTERNAL_READER
DENY SELECT ON vwNPSResponsesV2 TO DB_EXTERNAL_READER
--Check which objects the role have access to.
SELECT permission_name, state_desc, object_name(major_id), name
FROM sys.database_permissions p
INNER JOIN sys.database_principals dp
ON p.grantee_principal_id = dp.principal_id
where dp.name = 'DB_EXTERNAL_READER'
--Make sure whenever you drop/create objects you need to re-grant the permissions.
--Use sp_addrolemember to add the user to the role
sp_addrolemember [ @rolename = ] 'role', [ @membername = ] 'security_account'
To check the access
After few search and scrubbing the code available on the we, we come with below snippet useful.
--Create a role
CREATE ROLE DB_EXTERNAL_READER
--Grant permission to the role on objects
GRANT SELECT ON vwNpsResponses TO DB_EXTERNAL_READER
GRANT SELECT ON vwNPSResponsesV2 TO DB_EXTERNAL_READER
DENY SELECT ON vwNPSResponsesV2 TO DB_EXTERNAL_READER
--Check which objects the role have access to.
SELECT permission_name, state_desc, object_name(major_id), name
FROM sys.database_permissions p
INNER JOIN sys.database_principals dp
ON p.grantee_principal_id = dp.principal_id
where dp.name = 'DB_EXTERNAL_READER'
--Make sure whenever you drop/create objects you need to re-grant the permissions.
--Use sp_addrolemember to add the user to the role
sp_addrolemember [ @rolename = ] 'role', [ @membername = ] 'security_account'
To check the access
WITH perms_cte as ( SELECT USER_NAME(p.grantee_principal_id) AS principal_name, dp.principal_id, dp.type_desc AS principal_type_desc, p.class_desc, OBJECT_NAME(p.major_id) AS object_name, p.permission_name, p.state_desc AS permission_state_desc FROM sys.database_permissions p INNER JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id ),perms AS( --users SELECT p.principal_name, p.principal_type_desc, p.class_desc, p.[object_name], p.permission_name, p.permission_state_desc, cast(NULL as sysname) as role_name FROM perms_cte p WHERE principal_type_desc <> 'DATABASE_ROLE' UNION --role members SELECT rm.member_principal_name, rm.principal_type_desc, p.class_desc, p.object_name, p.permission_name, p.permission_state_desc,rm.role_name FROM perms_cte p RIGHT OUTER JOIN ( SELECT role_principal_id, dp.type_desc as principal_type_desc, member_principal_id,user_name(member_principal_id) as member_principal_name,user_name(role_principal_id) as role_name--,* FROM sys.database_role_members rm INNER JOIN sys.database_principals dp ON rm.member_principal_id = dp.principal_id ) rm ON rm.role_principal_id = p.principal_id) SELECT * FROM Perms WHERE principal_name = 'User name'