Export (0) Print
Expand All

sys.database_principals (Transact-SQL)

Returns a row for each security principal in a SQL Server database.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.

Column name

Data type

Description

name

sysname

Name of principal, unique within the database.

principal_id

int

ID of principal, unique within the database.

type

char(1)

Principal type:

S = SQL user

U = Windows user

G = Windows group

A = Application role

R = Database role

C = User mapped to a certificate

K = User mapped to an asymmetric key

type_desc

nvarchar(60)

Description of principal type.

SQL_USER

WINDOWS_USER

WINDOWS_GROUP

APPLICATION_ROLE

DATABASE_ROLE

CERTIFICATE_MAPPED_USER

ASYMMETRIC_KEY_MAPPED_USER

default_schema_name

sysname

Name to be used when SQL name does not specify a schema. Null for principals not of type S, U, or A.

create_date

datetime

Time at which the principal was created.

modify_date

datetime

Time at which the principal was last modified.

owning_principal_id

int

ID of the principal that owns this principal. All principals except Database Roles must be owned by dbo.

sid

varbinary(85)

SID (Security Identifier) of the principal. NULL for SYS and INFORMATION SCHEMAS.

is_fixed_role

bit

If 1, this row represents an entry for one of the fixed database roles: db_owner, db_accessadmin, db_datareader, db_datawriter, db_ddladmin, db_securityadmin, db_backupoperator, db_denydatareader, db_denydatawriter.

authentication_type

int

Signifies authentication type.

Value

Description

0

No authentication

1

Instance authentication

2

Database authentication

3

Windows Authentication

Applies to: SQL Server 2012 through SQL Server 2014.

authentication_type_desc

nvarchar(60)

Description of the authentication type.

Value

Description

NONE

No authentication

INSTANCE

Instance authentication

DATABASE

Database authentication

WINDOWS

Windows Authentication

Applies to: SQL Server 2012 through SQL Server 2014.

default_language_name

sysname

Signifies the default language for this principal.

Applies to: SQL Server 2012 through SQL Server 2014.

default_language_lcid

int

Signifies the default LCID for this principal.

Applies to: SQL Server 2012 through SQL Server 2014.

The PasswordLastSetTime properties are available on all supported configurations of SQL Server, but the other properties are only available when SQL Server is running on Windows Server 2003 and both CHECK_POLICY and CHECK_EXPIRATION are enabled. See Password Policy for more information.

Any user can see their own user name, the system users, and the fixed database roles. To see other users, requires ALTER ANY USER, or a permission on the user. To see user-defined roles, requires ALTER ANY ROLE, or membership in the role.

A: Listing all the permissions of database principals

The following query lists the permissions explicitly granted or denied to database principals.

Important note Important

The permissions of fixed database roles do not appear in sys.database_permissions. Therefore, database principals may have additional permissions not listed here.

SELECT pr.principal_id, pr.name, pr.type_desc, 
    pr.authentication_type_desc, pe.state_desc, pe.permission_name
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
    ON pe.grantee_principal_id = pr.principal_id;

B: Listing permissions on schema objects within a database

The following query joins sys.database_principals and sys.database_permissions to sys.objects and sys.schemas to list permissions granted or denied to specific schema objects.

SELECT pr.principal_id, pr.name, pr.type_desc, 
    pr.authentication_type_desc, pe.state_desc, 
    pe.permission_name, s.name + '.' + o.name AS ObjectName
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
    ON pe.grantee_principal_id = pr.principal_id
JOIN sys.objects AS o
    ON pe.major_id = o.object_id
JOIN sys.schemas AS s
    ON o.schema_id = s.schema_id;
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft