Export (0) Print
Expand All
0 out of 5 rated this helpful - Rate this topic

sys.server_principals (Transact-SQL)

Contains a row for every server-level principal.

Column name

Data type

Description

name

sysname

Name of the principal. Is unique within a server.

principal_id

int

ID number of the Principal. Is unique within a server.

sid

varbinary(85)

SID (Security-IDentifier) of the principal. If Windows principal, then it matches Windows SID.

type

char(1)

Principal type:

S = SQL login

U = Windows login

G = Windows group

R = Server role

C = Login mapped to a certificate

K = Login mapped to an asymmetric key

type_desc

nvarchar(60)

Description of the principal type:

SQL_LOGIN

WINDOWS_LOGIN

WINDOWS_GROUP

SERVER_ROLE

CERTIFICATE_MAPPED_LOGIN

ASYMMETRIC_KEY_MAPPED_LOGIN

is_disabled

int

1 = Login is disabled.

create_date

datetime

Time at which the principal was created.

modify_date

datetime

Time at which the principal definition was last modified.

default_database_name

sysname

Default database for this principal.

default_language_name

sysname

Default language for this principal.

credential_id

int

ID of a credential associated with this principal. If no credential is associated with this principal, credential_id will be NULL.

owning_principal_id

int

The principal_id of the owner of a server role. NULL if the principal is not a server role.

is_fixed_role

bit

Returns 1 if the principal is one of the fixed server roles.

Any login can see their own login name, the system logins, and the fixed server roles. To see other logins, requires ALTER ANY LOGIN, or a permission on the login. To see user-defined server roles, requires ALTER ANY SERVER ROLE, or membership in the role.

The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. For more information, see Metadata Visibility Configuration.

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

Important note Important

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

SELECT pr.principal_id, pr.name, pr.type_desc, 
    pe.state_desc, pe.permission_name 
FROM sys.server_principals AS pr 
JOIN sys.server_permissions AS pe 
    ON pe.grantee_principal_id = pr.principal_id;
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.