Archive

Posts Tagged ‘server role members’

Using Catalog Views – sys.server_role_members & sys.database_role_members

June 24, 2011 Leave a comment

sys.server_role_members contains the membership of each server principal with server roles. It returns a pair of role_principal_id and member_principal_id for each membership as below:

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT role_principal_id, member_principal_id

FROM   sys.server_role_members

Result Set:

role_principal_id member_principal_id

—————– ——————-

3                 1

3                 259

3                 260

3                 261

3                 262

9                 279

 

(6 row(s) affected)

sys.server_role_members can be joined with sys.server_principals to retrieve role/member name:

SELECT        A.name AS 'Role', B.name AS 'Login'

FROM          sys.server_role_members C

INNER JOIN    sys.server_principals A ON A.principal_id = C.role_principal_id

INNER JOIN    sys.server_principals  B ON B.principal_id = C.member_principal_id

Result Set:

Role                 Login

——————-  ———————-

Sysadmin             sa

Sysadmin             NT AUTHORITY\SYSTEM

Sysadmin             NT SERVICE\MSSQLSERVER

Sysadmin             ***********************

Sysadmin             NT SERVICE\SQLSERVERAGENT

Dbcreator            Vishal

 

(6 row(s) affected)

sys.server_role_members contains information for server level roles, for database level roles sys.database_role_members can be used. It returns a pair of role_principal_id and member_principal_id for each membership:

SELECT role_principal_id, member_principal_id

FROM   sys.database_role_members

Result Set:

role_principal_id member_principal_id

—————– ——————-

16384             1

16384             5

16384             6

16385             5

16385             6

16386             5

16387             5

16387             6

16389             5

16390             5

16390             6

16391             5

16391             6

 

(13 row(s) affected)

It can be joined with sys.database_principals to retrieve role/member name as below:

SELECT        A.name AS 'Role', B.name AS 'User'

FROM          sys.database_role_members C

INNER JOIN    sys.database_principals A ON A.principal_id = C.role_principal_id

INNER JOIN    sys.database_principals B ON B.principal_id = C.member_principal_id

Result Set:

Role                 User

——————– ——————–

db_owner             dbo

db_owner             sqladmin

db_accessadmin       sqladmin

db_securityadmin     sqladmin

db_ddladmin          sqladmin

db_backupoperator    sqladmin

db_datareader        sqladmin

db_datawriter        sqladmin

db_owner             NewUser

db_accessadmin       NewUser

db_ddladmin          NewUser

db_datareader        NewUser

db_datawriter        NewUser

 

(13 row(s) affected)

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe