Category: SQL Server 2011 (Denali)

sys.sql_logins: Returns one row for each SQL Server authentication login. It will return all the below columns.

• name
• principal_id
• sid
• type
• type_desc
• is_disabled
• create_date
• modify_date
• default_database_name
• default_language_name
• credential_id
• is_policy_checked
• is_expiration_checked
• password_hash

You can query sys.sql_logins to get all the below information.

SQL Logins which are disabled:

SELECT name  
FROM [sys].[sql_logins] 
WHERE [is_disabled] = 1; 

SQL Server Logins which adhere the password policy:

SELECT name  
FROM [sys].[sql_logins] 
WHERE [is_policy_checked] = 1;

SQL Server Logins which do not adhere to the password policy

SELECT name  
FROM [sys].[sql_logins] 
WHERE [is_policy_checked] = 0;

SQL Logins which do not adhere to password expiration

SELECT name  
FROM [sys].[sql_logins] 
WHERE [is_policy_checked] = 0 
   OR  ([is_policy_checked] = 1 AND [is_expiration_checked] = 0);