INFORMATICS

The Best

List users in SQL Server database

User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

Users, logins, hash password

SQL Server: Find Logins in SQL Server

SELECT * FROM master.sys.sql_logins;

The sys.sql_logins view contains the following columns:

ColumnExplanation
name This is the login_name that was assigned in CREATE LOGIN statement
principal_id Numeric value
sid This is the sid that was assigned in CREATE LOGIN statement
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
is_disabled 0 or 1
create_date Date/time when Login was created using the CREATE LOGIN statement
modify_date Date/time when Login was last modified using the ALTER LOGIN statement
default_database_name This is the default database assigned in CREATE LOGIN statement or ALTER LOGIN statement
default_language_name This is the default language assigned in CREATE LOGIN statement or ALTER LOGIN statement
credential_id This is the credential assigned in CREATE LOGIN statement or ALTER LOGIN statement
is_policy_checked 0 or 1, assigned by CREATE LOGIN statement or ALTER LOGIN statement
is_expiration_checked 0 or 1, assigned by CREATE LOGIN statement or ALTER LOGIN statement
password_hash Hashed value of the password

Older Version Compatibility

In older versions of SQL Server, you can retrieve all Logins using the SQL Server 2000 system tables such as the sys.syslogins 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.syslogins;

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

ColumnExplanation
sid This is the sid that was assigned in CREATE LOGIN statement
status Not applicable
createdate Date/time when Login was created using the CREATE LOGIN statement
updatedate Date/time when Login was last modified using the ALTER LOGIN statement
accdate Not applicable
totcpu Not applicable
totio Not applicable
spacelimit Not applicable
timelimit Not applicable
resultlimit Not applicable
name This is the login_name that was assigned in CREATE LOGIN statement
dbname Default database
password NULL
language Default language
denylogin 0 or 1
hasaccess 0 or 1
isntname 0 or 1
isntgroup 0 or 1
isntuser 0 or 1
sysadmin 0 or 1
securityadmin 0 or 1
serveradmin 0 or 1
setupadmin 0 or 1
processadmin 0 or 1
diskadmin 0 or 1
dbcreator 0 or 1
bulkadmin 0 or 1
loginname This is the login_name that was assigned in CREATE LOGIN statement

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:

ColumnExplanation
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:

ColumnExplanation
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

Login grants access to the server
User grants a login access to the database List users in SQL Server database

Query

select sp.name as login,
       sp.type_desc as login_type,
       sl.password_hash,
       sp.create_date,
       sp.modify_date,
       case when sp.is_disabled = 1 then 'Disabled'
            else 'Enabled' end as status
from sys.server_principals sp
left join sys.sql_logins sl
          on sp.principal_id = sl.principal_id
where sp.type not in ('G', 'R')
order by sp.name;

Columns

  • login - user name
  • login_type - principal type:
    • SQL_LOGIN - SQL login
    • WINDOWS_LOGIN - Windows login
    • CERTIFICATE_MAPPED_LOGIN - Login mapped to a certificate
    • ASYMMETRIC_KEY_MAPPED_LOGIN - Login mapped to an asymmetric key
  • password_hash - for SQL logins hashed password with SHA-512
  • create_date - date the login was added
  • modify_date - date the login was last updated
  • status - status of the login
    • Enabled
    • Disabled

Rows

  • One row represents one user in the database
  • Scope of rows: all users in the database
  • Ordered by user name

Search