Managing SQL Role

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

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'