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.