HomeProgramSQL ServerFind User in 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

See more:

  1. Find Login in SQL Server
  2. The CREATE USER command in SQL Server

Previous article: DROP USER command in SQL Server

The following article: Use annotations in SQL Server

4.1 ★ | 15 Vote
MS SQL ServerSQL Servermssql

You should read it

May be interested

  • Use annotations in SQL ServerPhoto of Use annotations in SQL Server
    this article will show you how to use annotations in sql server statements.
  • LITERAL in SQL ServerPhoto of LITERAL in SQL Server
    the article details how to use literal - hang (strings, integers, decimals, and datetime values) in sql server.
  • Declare variables in SQL ServerPhoto of Declare variables in SQL Server
    sql server fully exists the concepts of data types, variables and declarations of variables as other programming languages. the article will learn how to declare a variable, multiple variables and assign default values ​​to variables in sql server.
  • SEQUENCE in SQL ServerPhoto of SEQUENCE in SQL Server
    sequence is often used in databases because it is necessary for many applications. the article will provide syntax and examples of how to create and delete the sequence and its attributes in sql server.
  • FUNCTION (Function) in SQL ServerPhoto of FUNCTION (Function) in SQL Server
    the function in sql server is stored in the database so you can pass the parameters as well as return the values. the article will give you the syntax and examples of how to create and delete functions in sql server.
  • PROCEDURE (Procedure) in SQL ServerPhoto of PROCEDURE (Procedure) in SQL Server
    procedure is a program in the database that contains many statements that you save for later use but have different points from function. the article will give you the syntax and examples of how to create and delete procedures in sql server.

SQL Server