Returns a row for each security 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) of the principal. NULL for SYS and INFORMATION SCHEMAS
|
|
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.
|
|
authentication_type
|
int
|
Signifies authentication type.
|
Value
|
Description
|
|
0
|
No authentication
|
|
1
|
Instance authentication
|
|
2
|
Database authentication
|
|
3
|
Windows Authentication
|
Does not apply to SQL Azure.
|
|
authentication_type_desc
|
nvarchar(60)
|
Description of the authentication type.
|
Value
|
Description
|
|
NONE
|
No authentication
|
|
INSTANCE
|
Instance authentication
|
|
DATABASE
|
Database authentication
|
|
WINDOWS
|
Windows Authentication
|
Does not apply to SQL Azure.
|
|
default_language_name
|
sysname
|
Signifies the default language for this principal.
Does not apply to SQL Azure.
|
|
default_language_lcid
|
int
|
Signifies the default LCID for this principal.
Does not apply to SQL Azure.
|
Remarks
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.
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.
See Also