Tag Archives: Security

sys.sql_logins

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);