Archive

Posts Tagged ‘Database principals’

Using Catalog Views – sys.server_role_members & sys.database_role_members

24.06.2011 No comments

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 AUTHORITYSYSTEM

Sysadmin             NT SERVICEMSSQLSERVER

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

Sysadmin             NT SERVICESQLSERVERAGENT

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

Using Catalog Views – sys.database_principals

23.06.2011 No comments

While sys.server_principals contains all logins/roles created on the server. sys.database_principals contains all users/roles in the current database. Some of the columns returned by sys.database_principals are as below:

1. Name – name of the principal – user/role name,

2. Principal_id – id of the principal,

3. Type – type of the principal – S = SQL user (mapped to a SQL Login), U = Windows user (mapped to a Windows Login), G = Windows group (mapped to a Windows Group Login), A = Application role, R = Database role, C = Certificate mapped user, K = Asymmetric key mapped user,

4. Type_desc – Textual description of 'Type',

5. Default_schema_name – Default schema name to be used when schema name is not specified,

6. Create/Modify_date – Creation/Modification date,

7. Sid – Security identifier, applicable only to SQL User, Windows User and Windows Group,

8. Is_fixed_role – 1 if it’s a fixed database role

 

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

 

SELECT name, principal_id, type, default_schema_name,

       create_date, modify_date, sid, is_fixed_role

FROM   sys.database_principals

Partial Result Set:

image

Hope This Helps!

Vishal