audit-labs/audit-tools

 1/*
 2References:
 31. https://learn.microsoft.com/en-us/sql/relational-databases/security/password-policy
 42. https://learn.microsoft.com/en-us/sql/t-sql/functions/loginproperty-transact-sql
 5*/
 6
 7SELECT
 8    name,
 9    principal_id,
10    sid,
11    type,
12    type_desc,
13    is_disabled,
14    create_date,
15    modify_date,
16    default_database_name,
17    default_language_name,
18    credential_id,
19    is_policy_checked,
20    is_expiration_checked,
21    password_hash,
22    LOGINPROPERTY(name, 'IsMustChange') AS IsMustChange,
23    LOGINPROPERTY(name, 'IsLocked') AS IsLocked,
24    LOGINPROPERTY(name, 'LockoutTime') AS LockoutTime,
25    LOGINPROPERTY(name, 'PasswordLastSetTime') AS PasswordLastSetTime,
26    LOGINPROPERTY(name, 'IsExpired') AS IsExpired,
27    LOGINPROPERTY(name, 'BadPasswordCount') AS BadPasswordCount,
28    LOGINPROPERTY(name, 'BadPasswordTime') AS BadPasswordTime,
29    LOGINPROPERTY(name, 'HistoryLength') AS HistoryLength
30FROM sys.sql_logins;