Have you ever wanted to get a list of all of the SQL Server Logins for your SQL Database Server?
You can see the list of Login IDs by opening SQL Server Management Studio and expanding Security / Logins, but if you want to be able to copy them to an Excel sheet or anywhere else, the easiest way is to use a SQL query instead.
This can be helpful if you have multiple environments and want to get a list of ID’s/Groups that have been granted access to SQL and you want to compare across environments, or simply for Audit purposes.
This query returns the same list you should see in the Security / Logins node in SQL Server Management Studio.
/* Get All SQL Server Logins */
SELECT sp.name as 'Login Name', sp.principal_id AS 'Principal ID',
sp.type_desc as 'Type', sp.create_date AS 'Create Date',
CASE
WHEN sp.is_disabled = 0 THEN 'Enabled'
WHEN sp.is_disabled = 1 THEN 'Disabled'
ELSE 'Unknown'
END as 'Status'
FROM sys.server_principals AS sp left join
sys.sql_logins AS sl ON sp.principal_id = sl.principal_id
WHERE sp.type not in ('C','R')
ORDER BY sp.name
| Column | Description |
|---|---|
| name | This is the login_name that was assigned in the CREATE LOGIN statement |
| principal_id | Numeric value automatically assigned to the ID when created |
| sid | This is the sid that was automatically assigned to the ID when created |
| type | Type of principal S = SQL Server User U = Windows User G = Windows Group A = Application role C = Certificate mapped K = Asymmetric key mapped R = Database role |
| type_desc | Description for type of principal |
| is_disabled | 0 or 1 (0 = not disabled, 1 = disabled) |
| create_date | Date/time when Login was created using the CREATE LOGIN statement |
| modify_date | Date/time when Login was 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 | Hash value of the password |