SQL Server 2005: The loginproperty builtin function

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 password
BadPasswordTime - the time when the last attempt to login with an incorrect password occurred
HistoryLength - the length of the password history tracked by the Windows password policy
IsExpired - indicates whether the current login password has expired
IsLocked - indicates whether the login account was locked out as a result of too many attempts to login with an incorrect password
IsMustChange - indicates whether the login password must be changed at the time of the next login
LockoutTime - indicates the date when the login account was locked out
PasswordLastSetTime - the date when the current password was set
PasswordHash - 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');

Comments (3)

  1. Kyle says:

    Is there anything like ‘PasswordLastSetTime’ available in SQL 7 and 2000? or is there a way to programatically getting this info?

    Thank you.

  2. No, only SQL Server 2005 keeps track of this information.

Skip to main content