In this post, I'd like to describe a builtin function that is very useful for login management: loginproperty. Loginproperty accepts two arguments: the first is the name of the login for which information is requested and the second is a property name that specifies what information should be retrieved. The names of the properties are case insensitive, so property names like BadPasswordCount and badpasswordcount are equivalent. The return data type of the function is variant. Here's the list of properties that can be retrieved with loginproperty:
BadPasswordCount - the current number of consecutive attempts to login with an incorrect passwordBadPasswordTime - the time when the last attempt to login with an incorrect password occurredHistoryLength - the length of the password history tracked by the Windows password policyIsExpired - indicates whether the current login password has expiredIsLocked - indicates whether the login account was locked out as a result of too many attempts to login with an incorrect passwordIsMustChange - indicates whether the login password must be changed at the time of the next loginLockoutTime - indicates the date when the login account was locked outPasswordLastSetTime - the date when the current password was setPasswordHash - retrieves the hash of the password
Note that, with the exception of PasswordHash and PasswordLastSetTime, all these properties are related to the password policy settings and are only meaningful on Windows 2003, if the corresponding policy enforcement settings are enforced on the login (CHECK_POLICY, CHECK_EXPIRATION are on). Obtaining the PasswordHash property requires the CONTROL SERVER permission; for the other properties, VIEW permission on the login is required.
And here's a simple example of calling this function to get the time when alice has last changed her password:
select loginproperty('alice', 'PasswordLastSetTime');