Enumerate Database-Role Membership

Hey Guy’s

here i am with my 1st SQL-Server Post!

If you want to know the membership of a database role, to what other database roles the role
belongs to, i have a good satatement for you.

SELECT dbrole1.name AS dbrole1_name
       , dbrole1.principal_id 
       , dbrm.role_principal_id
       , dbrole2.name            AS dbrole2_name
FROM sys.database_principals AS dbrole1

JOIN sys.database_role_members AS dbrm
  ON dbrm.member_principal_id = dbrole1.principal_id

JOIN sys.database_principals AS dbrole2
  ON dbrole2.principal_id = dbrm.role_principal_id

WHERE dbrole1.name LIKE 'myRole%'

ORDER BY dbrole1.name ASC

Leave a Reply

Your email address will not be published. Required fields are marked *

Proudly powered by WordPress | Theme: Baskerville 2 by Anders Noren.

Up ↑