0

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.

enter image description here

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:

enter image description here

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.

  • 1
    You would need to use recursion because you can always add a role to another role - it's a never-ending rabbit-hole. – Aaron Bertrand Sep 3 '15 at 12:08
2
;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

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.