LOGINPROPERTY Built-In function


SQL Server 2005/2008 built-in function LOGINPROPERTY can help us retrieve the important information of login properties.

We can retrieve the below important details using LOGINPROPERTY function.
1. Date of last password change
2. Login is locked or not
3. Loign password expired
4. Need to change password at next login or not
5. Count of consecutive failed login attempts
6. DateTime when the last login failed
7. DateTime when the login was locked out
8. Password hash
9. Returns the number of days until the password expires. LOGINPROPERTY(‘sa’, ‘DaysUntilExpiration’) argument is only available with SQL Server 2008

SELECT
name,
CASE LOGINPROPERTY(name, 'IsLocked')
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE 'Unknown'
END as IsAccountLocked,
LOGINPROPERTY(name, 'PasswordLastSetTime') as PasswordLastSetDate,
LOGINPROPERTY(name, 'BadPasswordCount') as CountOfFailedLoginAttempts,
LOGINPROPERTY(name, 'BadPasswordTime') as LastFailedLoginTime,
LOGINPROPERTY(name, 'LockoutTime') as LoginLockedOutDateTime,
CASE LOGINPROPERTY(name, 'IsExpired')
WHEN 0 THEN 'Password is not expired'
WHEN 1 THEN 'Password is not expired,change it'
ELSE 'Unknown'
END as PasswordExpired,
CASE LOGINPROPERTY(name, 'IsMustChange')
WHEN 0 THEN 'Must not change password at next login'
WHEN 1 THEN 'Must change password at next login'
ELSE 'Unknown'
END as PasswordChangeOnNextLogin,
LOGINPROPERTY(name, 'DaysUntilExpiration') as 'DaysUntilthePasswordExpires', --New Property in SQL Server 2008
LOGINPROPERTY(name, 'PasswordHash') as PasswordHash
From sys.sql_logins
order by name

Output

Advertisements

2 thoughts on “LOGINPROPERTY Built-In function

  1. JG

    I have looked for the past couple of months for something like this. I would do a google search and I finally started to click on the 10th page in the search since all previous pages never had this info. Awsome! And Thank You

    Reply

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s