Audit on SQL login for when the last password change date

This post was formerly published at my beloved old blog "On the quest of a DBA's adventure......"

One day, my system accountant comes over to ask me for help. He was hassled by an external auditor who required him for proof on when was the last password change on the finance system service account. Well, for domain account password change, one could find it out by looking at the attribute editor of the entity. However, since Microsoft still have the SQL authentication option available, the SQL logins are still alive and as a result, you would scratch your head on where to get this sort of information.

Well, I found the following built-in security function is pretty handy for my situation.

Syntax

There is a number of option, which is pretty self-explained, on what could you find out for a given login:

• BadPasswordCount
• BadPasswordTime
• DaysUntilExpiration
• DefaultDatabase
• DefaultLanguage
• HistoryLength
• IsExpired
• IsLocked
• IsMustChange
• LockoutTime
• PasswordHash
• PasswordLastSetTime

For my case, I do the following query for my friend:

Now, he can tell the auditor down to the seconds on when he changed the password!! Another happy customer.....

Here is the reference from MSDN - LOGINPROPERTY (Transact-SQL)