Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Returns information about login policy settings.
Applies to: SQL Server (SQL Server 2008 through current version).
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 and HistoryLength are of type int.
BadPasswordTime, 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.
PasswordHashAlgorithm is of type int.
0 if a SQL7.0 hash
1 if a SHA-1 hash
2 if a SHA-2 hash
NULL if the login is not a valid SQL Server login
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, PasswordHashAlgorithm, 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.
A. Checking whether a login must change its password
The following example checks whether SQL Server login John3 must change its password the next time it connects to an instance of SQL Server.
SELECT LOGINPROPERTY('John3', 'IsMustChange'); GO
B. Checking whether a login is locked out
The following example checks whether SQL Server login John3 is locked.
SELECT LOGINPROPERTY('John3', 'IsLocked'); GO