sys.sql_logins, Password Expiration

Here's a good one:


   Logon Error: 18487, Severity: 14, State: 1. 
  Logon Login failed for user 'loginname'. Reason: The password of the account has   
  expired. [CLIENT: <named pipe>]


Your company's web application is running, but... the password has expired for the SQL login.  The 'Enforce password policy' and 'Enforce password expiration' configurations were introduced in v2005.  Review your SQL login properties, and be certain that you know if either of these policies are being enforced.  Not only that, I encourage you to check the varied statistics on your logins;  # of failed login attempts, last failed login, how many days until X login's password expires, which logins are locked out, etc. :

  SELECT

     name [SQL Login],

     CASE WHEN is_policy_checked = 0 THEN 'Disabled'

        WHEN is_policy_checked = 1 THEN 'Enabled' END 

     [Enforce Password Policy],

     CASE WHEN is_expiration_checked = 0 THEN 'Disabled' 

        WHEN is_expiration_checked = 1 THEN 'Enabled' END   

     [Enforce Password Expiration],

     CASE LOGINPROPERTY(name, 'IsLocked')

        WHEN 0 THEN 'No'

        WHEN 1 THEN 'Yes'

        ELSE 'Unknown' END as IsLocked,

     LOGINPROPERTY(name, 'PasswordLastSetTime') 

     [PasswordSetDate],

     LOGINPROPERTY(name, 'BadPasswordCount') 

     [FailedLoginAttempts],

     LOGINPROPERTY(name, 'BadPasswordTime') [LastFailedLogin],

     LOGINPROPERTY(name, 'LockoutTime') [LockedOutDate],

     CASE LOGINPROPERTY(name, 'IsExpired')

        WHEN 0 THEN 'Password is not expired'

        WHEN 1 THEN 'Password is not expired, change it'

        ELSE 'Unknown' END [PasswordExpired],

     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 [PasswordChangeOnNextLogin],

     LOGINPROPERTY(name, 'DaysUntilExpiration')    

     [DaysUntilPasswordExpires]

   FROM 

     sys.sql_logins

   WHERE 

     [name] NOT LIKE '##%'

   ORDER BY 

     [name]