I can see the permissions of a role in a database using the scripts in List all permissions for a given role?
However, It does not show me when the role belongs to a another role.
For instance, on the picture below you can see that db_integrationEngine has the permissions of other roles too. It is db_datareader and db_datawriter.
However when I run the script below, I can't see this.
here is the script I am using:
SELECT DISTINCT rp.name,
ObjectType = rp.type_desc,
PermissionType = pm.class_desc,
pm.permission_name,
pm.state_desc,
ObjectType = CASE
WHEN obj.type_desc IS NULL
OR obj.type_desc = 'SYSTEM_TABLE' THEN
pm.class_desc
ELSE obj.type_desc
END,
[ObjectName] = Isnull(ss.name, Object_name(pm.major_id))
FROM sys.database_principals rp
INNER JOIN sys.database_permissions pm
ON pm.grantee_principal_id = rp.principal_id
LEFT JOIN sys.schemas ss
ON pm.major_id = ss.schema_id
LEFT JOIN sys.objects obj
ON pm.[major_id] = obj.[object_id]
WHERE rp.type_desc = 'DATABASE_ROLE'
AND pm.class_desc <> 'DATABASE'
AND rp.name <> 'PUBLIC'
ORDER BY rp.name,
rp.type_desc,
pm.class_desc
The script above, gives me the result on the picture below:
Scripts to add and remove one role to another
sp_addrolemember [db_datareader], [db_IntegrationEngine]
go
sp_addrolemember [db_datawriter], [db_IntegrationEngine]
go
sp_droprolemember [db_datareader], [db_IntegrationEngine]
go
sp_droprolemember [db_datawriter], [db_IntegrationEngine]
go
Ideally I am thinking about a recursive CTE that would go through each role and gets its permissions and when find another role inside it, do the same.
Just so far my attempts to generate such scripts were not successful, any help?
It does not need to be a recursive CTE as long as I can see the roles that are contained inside another roles.