HOW TO FIND USER DEFINED DATABASE ROLES AND THE PRIVILEGES ASSOCIATED WITH THE ROLE
There can be many user defined database roles in a database which are created by a DBA depending upon the requirement from support teams.
Instead of granting a particular access to any user , it is always to better to create a database role and grant the privileges to the role. DBA will have more control on security when lots of users want similar kind of access.
Challenge:
Challenge for a DBA comes when refresh of a database takes place having lots of user defined roles and backup used is the old one which doesn't have any. We have to make sure after refresh all the roles and Users are intact as it was before the refresh.
To achieve the above defined goal, we should save the db roles with its respective privileges by scripting them in order to create them once the refresh/restore is performed by a DBA.
Below query is used to find the user defined roles with its respective privileges and also provide the script to be used as next course of action.
select
name as principal_name,
SUSER_SNAME(sid) AS LOGIN_NAME ,
principal_id,
type_desc,
'NULL' [Class Desc],
'NULL' [Object Name],
'NULL' [Permission Name],
'NULL' [Permission Type],
'sp_addrole ['+name+']' collate SQL_Latin1_General_CP1_CI_AS AS [Script]
from sys.database_principals
where type='R' and is_fixed_role =0 and name not like 'public' and
principal_id in
(select distinct grantee_principal_id from sys.database_permissions)
UNION ALL
SELECT
USER_NAME(p.grantee_principal_id) AS principal_name,
SUSER_SNAME(dp.sid) AS LOGIN_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,
Case
when OBJECT_NAME(p.major_id) is not null
then 'grant '+p.permission_name +' on object::['+OBJECT_NAME(p.major_id)+'] to '+USER_NAME(p.grantee_principal_id)+''
when OBJECT_NAME(p.major_id) is null
then 'grant '+p.permission_name +' to '+USER_NAME(p.grantee_principal_id)+''
End as 'script'
from sys.database_permissions p
inner JOIN sys.database_principals dp on
p.grantee_principal_id = dp.principal_id where dp.type_desc like
'DATABASE_ROLE' and dp.principal_id>0
GO
name as principal_name,
SUSER_SNAME(sid) AS LOGIN_NAME ,
principal_id,
type_desc,
'NULL' [Class Desc],
'NULL' [Object Name],
'NULL' [Permission Name],
'NULL' [Permission Type],
'sp_addrole ['+name+']' collate SQL_Latin1_General_CP1_CI_AS AS [Script]
from sys.database_principals
where type='R' and is_fixed_role =0 and name not like 'public' and
principal_id in
(select distinct grantee_principal_id from sys.database_permissions)
UNION ALL
SELECT
USER_NAME(p.grantee_principal_id) AS principal_name,
SUSER_SNAME(dp.sid) AS LOGIN_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,
Case
when OBJECT_NAME(p.major_id) is not null
then 'grant '+p.permission_name +' on object::['+OBJECT_NAME(p.major_id)+'] to '+USER_NAME(p.grantee_principal_id)+''
when OBJECT_NAME(p.major_id) is null
then 'grant '+p.permission_name +' to '+USER_NAME(p.grantee_principal_id)+''
End as 'script'
from sys.database_permissions p
inner JOIN sys.database_principals dp on
p.grantee_principal_id = dp.principal_id where dp.type_desc like
'DATABASE_ROLE' and dp.principal_id>0
GO
