login_name.

Is the name of a SQL Server login for which login property status will be returned.

propertyname

Is an expression that contains the property information to be returned for the login. propertyname can be one of the following values.

Value	Description
IsLocked	Returns information that will indicate whether the login is locked.
IsExpired	Returns information that will indicate whether the login has expired.
IsMustChange	Returns information that will indicate whether the login must change its password the next time it connects.
BadPasswordCount	Returns the number of consecutive attempts to log in with an incorrect password.
BadPasswordTime	Returns the time of the last attempt to log in with an incorrect password.
HistoryLength	Returns the length of time the login has been tracked using the password-policy enforcement mechanism.
LockoutTime	Returns the date when the SQL Server login was locked out because it had exceeded the permitted number of failed login attempts.
PasswordLastSetTime	Returns the date when the current password was set.
PasswordHash	Returns the hash of the password.
DaysUntilExpiration	Returns the number of days until the password expires.
DefaultDatabase	Returns the SQL Server login default database as stored in metadata or master if no database is specified. Returns NULL for non-SQL Server provisioned users, for example, Windows authenticated users.
DefaultLanguage	Returns the login default language as stored in metadata. Returns NULL for non-SQL Server provisioned users, for example, Windows authenticated users.

This built-in function returns information about the password policy settings of a SQL Server login. The names of the properties are not case sensitive, so property names such as BadPasswordCount and badpasswordcount are equivalent. The values of the PasswordHash and PasswordLastSetTime properties are available on all supported configurations of SQL Server, but the other properties are only available when SQL Server is running on Windows Server 2003 and both CHECK_POLICY and CHECK_EXPIRATION are enabled. For more information, see Password Policy.

Requires VIEW permission on the login. When requesting the password hash, also requires CONTROL SERVER permission.

Data type depends on requested value.

IsLocked, IsExpired, and IsMustChange are of type int.

• 1 if the login is in the specified state.
  
• 0 if the login is not in the specified state.
  

BadPasswordCount is of type int.

BadPasswordTime, HistoryLength, LockoutTime, PasswordLastSetTime are of type datetime.

PasswordHash is of type varbinary.

NULL if the login is not a valid SQL Server login.

DaysUntilExpiration is of type int.

0 if the login is expired or if it will expire on the day when queried.

-1 if the local security policy in Windows never expires the password.

NULL if the CHECK_POLICY or CHECK_EXPIRATION is OFF for a login, or if the operating system does not support the password policy.

A. Checking whether a login must change its password

SELECT LOGINPROPERTY('John3', 'IsMustChange');
GO

B. Checking whether a login is locked out

SELECT LOGINPROPERTY('John3', 'IsLocked');
GO

Reference

Other Resources

Help and Information