MS SQL Server: Find User in SQL Server

Do any queries on SQL Server return all User created?

On SQL Server there is a system table called sys.database_principals. You can run the above query and the result is all Users created on the database and information about them.

To retrieve all Users in SQL Server, run the following command:

 SELECT * 
 FROM master.sys.database_principals; 

System sys.database_principals includes the following columns:

COLLECTION NOTE User name created in the CREATE USER principal_id command principal code (numeric value) type

Type principal:

S = User of SQL Server U = User of Windows G = Windows group A = Application role R = Database role C = Certificate has mapped K = Asymmetric key is mapped

type_desc

Description of principal types:

SQL_USER WINDOWS_USER WINDOWS_GROUP APPLICATION_ROLE DATABASE_ROLE CERTIFICATE_MAPPED_USER ASSYMETRIC_KEY_MAPPED_USER

default_schema_name The name used when the schema is not defined create_date Date / Time created User by CREATE USER command modify_date User Date / Time edit owning_principal_id Id principal of User sid The value sid is specified in the CREATE LOGIN command is_fixed_role 0 or 1 authentication_type NONE, WINDOWS, INSTANCE default_language_name default_language_lcid

For older versions

In older versions of SQL Server, you can retrieve all Users by using the sys.sysusers table in SQL Server 2000. The command is as follows:

 SELECT * 
 FROM master.sys.sysusers; 

COLLECTION NOTE uid user Id (unique numeric value) status Not applicable User name created in CREATE USER sid command Sid value is specified in the command CREATE LOGIN roles Not created createdate Date / Time created User by CREATE USER updatedate command Date / Time to edit User altuid Not apply password Not apply gid Group id assigned to User environ Not applicable hasdbaccess Value 0 or 1 islogin Value 0 or 1 isntname Value 0 or 1 isntgroup Value 0 or 1 isntuser Value 0 or 1 issqluser Value 0 or 1 isaliased Value 0 or 1 issqlrole Value 0 or 1 isapprole Value 0 or 1

• Find Login in SQL Server
• The CREATE USER command in SQL Server