Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Using DMV to identify members roles

Some times is necessary retrieve a list of members of the roles of a database. Doing this is simple, using the DMV sys.database_role_members. Below follow an example of how to do this.

 

members_roles

Comments

Posted by Jason Brimhall on 12 August 2010

Thanks for the useful info.

Posted by Jonathan Roberts on 14 August 2010

Yes, but it would have been nice to have it as code that could be copied and pasted.

Posted by tilak_majety on 15 August 2010

sys.database_role_members is a DMV? i thought it is a system view

Posted by Douglas Osborne on 23 August 2010

SELECT

Roles.Role_ID,

Roles.Role_Name,

Roles.Member_ID,

Principals.Name AS Member_Name

FROM

(

SELECT

Principals.Name AS Role_Name,

Roles.Role_Principal_ID AS Role_ID,

Roles.Member_Principal_ID AS Member_ID

FROM SYS.Database_Principals Principals

INNER JOIN SYS.Database_Role_Members Roles ON Principals.Principal_ID = Roles.Role_Principal_ID

) Roles

INNER JOIN SYS.Database_Principals Principals ON Roles.Member_ID = Principals.Principal_ID

ORDER BY Member_Name

Posted by Jon.Morisi on 29 April 2011

thx Douglas, I was going to do that utill I scrolled down

Leave a Comment

Please register or log in to leave a comment.