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:

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:

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:

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:

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