Subscribe to this blog
Briefcase
Print
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.
|
|||||
|
|||||
|
Get Your Own BlogIf you would like to blog on SQLServerCentral.com then send an email to webmaster@sqlservercentral.com. Contact the authorAll BlogsFeedsArchives for this blogUsing DMV to identify members rolesSome 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.
CommentsLeave a Comment |
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