Question: Is there a query to run in SQL Server that will return all SQL Server Logins and information about those Logins?
Answer: In SQL Server, there is a catalog view (ie: system view) called sys.sql_logins. You can run a query against this system view that returns all of the Logins that have been created in SQL Server as well as information about these Logins.
To retrieve all Logins in SQL Server, you can execute the following SQL statement:
SELECT * FROM master.sys.sql_logins;
The sys.sql_logins view contains the following columns:
| Column | Explanation |
|---|---|
| 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:
| Column | Explanation |
|---|---|
| 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 |