Sql Server Find Users In Sql Server

SQL Server: Find Users in SQL Server

Question:Is there a query to run in SQL Server that will return all Users created?

Answer: In SQL Server, there is a system view called sys.database_principals. You can run a query against this system view that returns all of the Users that have been created in SQL Server as well as information about these Users.

To retrieve all Users in SQL Server, you can execute the following SQL statement:

SELECT *
FROM master.sys.database_principals;

The sys.database_principals view contains the following columns:

Column Explanation
name This is the user_name that was assigned in CREATE USER statement
principal_id Unique numeric value
type Type of principal S = SQL Server user U = Windows user G = Windows group A = Application role R = Database role C = Certificate mapped K = Asymmetric key mapped
type_desc Description for type of principal SQL_USER WINDOWS_USER WINDOWS_GROUP APPLICATION_ROLE DATABASE_ROLE CERTIFICATE_MAPPED_USER ASSYMETRIC_KEY_MAPPED_USER
default_schema_name Name to use when schema is not specified
create_date Date/time when User was created using the CREATE USER statement
modify_date Date/time when User was modified
owning_principal_id ID of the principal that owns this user
sid This is th sid that was assigned using the CREATE LOGIN statement
is_fixed_role 0 or 1
authentication_type NONE, WINDOWS, INSTANCE
default_language_name
default_language_lcid

Older Version Compatibility

In older versions of SQL Server, you can retrieve all Users using the SQL Server 2000 system tables such as the sys.sysusers table.

To retrieve all Users in SQL Server using the SQL Server 2000 system tables, you can execute the following SQL statement:

SELECT *
FROM master.sys.sysusers;

The sys.sysusers system table (SQL Server 2000) contains the following columns:

Column Explanation
uid Unique numeric value
status Not used
name This is the user_name that was assigned in CREATE USER statement
sid This is the sid that was assigned in CREATE LOGIN statement
roles Not used
createdate Date/time when User was created using the CREATE USER statement
updatedate Date/time when User was modified
altuid Not used
password No used
gid Group ID assigned to the User
environ Not used
hasdbaccess 0 or 1
islogin 0 or 1
isntname 0 or 1
isntgroup 0 or 1
isntuser 0 or 1
issqluser 0 or 1
isaliased 0 or 1
issqlrole 0 or 1
isapprole 0 or 1