Home > Catalog Views, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2 > Using Catalog Views – sys.database_principals

Using Catalog Views – sys.database_principals

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:


Hope This Helps!


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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: