Query below returns list of logins in current SQL Server instance.
Users vs logins
- Login grants access to the server
User grants a login access to the database List users in SQL Server database
One login can be associated with many users but only in different databases
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
Sample results
SSMS
You can vew logins using SQL Server Management studio. Expand Server -> Security -> Logins branch in Object Explorer.