SQL Server Login Properties

Just a quick post that you can use to check your SQL Server login properties.  Very helpful to keep an eye on the password policies and login state.  It's also something that you can use to see how many failed logins are occurring, and when. 

/* login properties */
SELECT
name [SQLLogin],
CASE WHEN is_policy_checked = 0 THEN 'Disabled'
                WHEN is_policy_checked = 1 THEN 'Enabled' END [EnforcePasswordPolicy],
CASE WHEN is_expiration_checked = 0 THEN 'Disabled' 
              WHEN is_expiration_checked = 1 THEN 'Enabled' END [EnforcePasswordExpiration],
CASE LOGINPROPERTY(name, 'IsLocked')
              WHEN 0 THEN 'No'
              WHEN 1 THEN 'Yes' ELSE 'Unknown' END [IsLocked],
LOGINPROPERTY(name, 'PasswordLastSetTime') [DatePasswordSet],
LOGINPROPERTY(name, 'BadPasswordCount') [FailedLogins],
LOGINPROPERTY(name, 'BadPasswordTime') [LastFailedLogin],
LOGINPROPERTY(name, 'LockoutTime') [DateLockedOut],
CASE LOGINPROPERTY(name, 'IsExpired')
             WHEN 0 THEN 'Password is not expired'
             WHEN 1 THEN 'Password is not expired, change it' ELSE 'Unknown' END [PwdExpired],
CASE LOGINPROPERTY(name, 'IsMustChange')
WHEN 0 THEN 'Must not change password at next login'
WHEN 1 THEN 'Must change password at next login' ELSE 'Unknown' END [PwdChangeNextLogin],
LOGINPROPERTY(name, 'DaysUntilExpiration') [DaysUntilPwdExpires]
FROM 
sys.sql_logins
WHERE 
[name] NOT LIKE '##%'
ORDER BY 
[name]


Take a look at this reference for more detailed LOGINPROPERTY information from MSFT:   
     https://msdn.microsoft.com/en-us/library/ms345412(v=sql.110).aspx