sys.database_principals (Transact-SQL)
Returns a row for each principal in a 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 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) if the principal is defined external to the database (type S, U, and G). Otherwise, NULL. |
is_fixed_role |
bit |
If 1, then 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. |
Permissions
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.
In SQL Server 2005 and later versions, 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.
See Also