List of role permissions including other roles - how can I find out the roles that belong to other roles?

Question

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.

Answer 1

;WITH theRoles (member_principal_id, role_principal_id) 
AS 
(
  SELECT 
   r.member_principal_id, 
   r.role_principal_id
  FROM sys.database_role_members r 
   UNION ALL
  SELECT 
   tr.member_principal_id, 
   rm.role_principal_id
  FROM sys.database_role_members rm 
            INNER JOIN theRoles tr 
                    ON rm.member_principal_id = tr.role_principal_id
)



select  the_role=A.name
       ,A.is_fixed_role
       ,role_member=B.name
       ,B.type_desc
from theRoles tr
        INNER JOIN sys.database_principals A 
                ON tr.role_principal_id = A.principal_id
        INNER JOIN sys.database_principals B 
                ON tr.member_principal_id = B.principal_id
GROUP BY A.name
       ,A.is_fixed_role
       ,B.name
       ,B.type_desc
order by a.is_fixed_role DESC
        ,a.name

and for testing the script above:

USE [DBA]
GO
CREATE ROLE [role_1]
GO
CREATE ROLE [role_2]
GO
CREATE ROLE [role_3]
GO
CREATE ROLE [role_10]
GO
CREATE ROLE [role_101]
GO
CREATE ROLE [role_1010]
GO

sp_addrolemember @rolename='db_datareader',@membername='role_1'
go
sp_addrolemember @rolename='db_datareader',@membername='role_2'
go
sp_addrolemember @rolename='db_datareader',@membername='role_3'
go
sp_addrolemember @rolename='role_1',@membername='role_10'
go
sp_addrolemember @rolename='role_10',@membername='role_101'
go
sp_addrolemember @rolename='role_10',@membername='role_1010'
go
sp_addrolemember @rolename='role_101',@membername='role_1010'
go