Share via

LOGINPROPERTY (Transact-SQL)

  • Article

Returns information about login policy settings.

Syntax

LOGINPROPERTY ( 'login_name' , 
                                    { 'IsLocked' | 'IsExpired' | 'IsMustChange'
                                    | 'BadPasswordCount' | 'BadPasswordTime' 
                                    | 'HistoryLength' | 'LockoutTime' 
                                    | 'PasswordLastSetTime' | 'PasswordHash' } 
                            )

Arguments

  • login_name
    Is the name of a SQL Server login for which login property status will be returned.
  • '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.

Remarks

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 2005, but the other properties are only available when SQL Server 2005 is running on Windows Server 2003 and both CHECK_POLICY and CHECK_EXPIRATION are enabled.

Permissions

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

Returns

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.

Examples

A. Checking whether a login must change its password

The following example checks whether SQL Server login WillisJO must change its password the next time it connects to an instance of SQL Server.

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

B. Checking whether a login is locked out

The following example checks whether SQL Server login SamirK is locked.

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

See Also

Reference

sys.server_principals (Transact-SQL)

Other Resources

Password Policy

Help and Information

Getting SQL Server 2005 Assistance