Identify SQL Server 2005 Standard Login Settings

By:   |   Updated: 2008-04-16   |   Comments (2)   |   Related: More > Security

Gaining insight into my SQL Server standard and Windows logins has historically been a challenge in terms of determining password changes, failed login attempts, etc.  I have noticed that you have experienced the same issue with your tip entitled 'When was the last time the SQL Server sa password changed' and I have noticed some information in the forums on the topic as well.  With SQL Server 2005 can I gain any more insight into the SQL Server standard logins part of the equation?  I know I can talk to my Network Admin counter parts for some of the Windows related login information.  Any and all recommendations would be appreciated.

As a matter of fact with the SQL Server 2005 built-in function LOGINPROPERTY, this command can help to address these issues and a few more when SQL Server is installed on a Windows 2003 server.  The best way to show the value in this SQL Server function is by outlining the problems it addresses then provide code snippets to take advantage of these features. Here are the problems that the SQL Server 2005 LOGINPROPERTY function addresses for standard logins:

  • Date when the password was set
  • Locked out standard login
  • Expired password
  • Must change password at next login
  • Count of consecutive failed login attempts
  • Time of the last failed login attempt
  • Amount of time since the password policy has been applied to the login
  • Date when the login was locked out
  • Password hash


In the code snippets below we are going to use the sa login as an example since it is created when SQL Server 2005 is installed with Windows and SQL Server authentication:

Date when the password was set

SELECT LOGINPROPERTY('sa', 'PasswordLastSetTime');

Locked out standard login

Result Set Legend
  • 0 - Login is not locked out
  • 1 - Login is locked out

Expired password

Result Set Legend
  • 0 - Password is not expired
  • 1 - Password is expired

Must change password at next login

Result Set Legend
  • 0 - Must not change password at next login
  • 1 - Must change password at next login

Count of consecutive failed login attempts

SELECT LOGINPROPERTY('sa', 'BadPasswordCount');
Time of the last failed login attempt
SELECT LOGINPROPERTY('sa', 'BadPasswordTime');

Amount of time since the password policy has been applied to the login

SELECT LOGINPROPERTY('sa', 'HistoryLength');

Date when the login was locked out

SELECT LOGINPROPERTY('sa', 'LockoutTime');

Password hash

SELECT LOGINPROPERTY('sa', 'PasswordHash');


According to SQL Server 2005 Books Online "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."  So if you run into an issue i.e. NULL result set validate the Windows operating system and the settings for the specific login before digging too far into the issue.

Next Steps

  • With the new SQL Server 2005 standard login functionality available be sure to take advantage of it when the needs arise.  I can distinctly remember many customers being frustrated with the standard SQL Server login features as compared to password policies for Windows logins.  Microsoft listened and delivered a number of new features to help manage standard SQL Server logins.
  • In terms of password changes, using the LOGINPROPERTY function is now a much easier means to determine if passwords have not changed recently.  If you have a policy related to changing passwords, be sure to run the appropriate scripts from this tip to validate the policy is being met with your standard logins.
  • Check out the related security and compliance tips on

Last Updated: 2008-04-16

get scripts

next tip button

About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is the Co-Founder, Editor and Author at, CTO @ Edgewood Solutions and a six time SQL Server MVP.

View all my tips
Related Resources

More SQL Server Solutions

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

Friday, February 13, 2009 - 12:26:50 PM - aprato Back To Top

You might have an out of date copy of the books online?

Friday, February 13, 2009 - 11:55:57 AM - JLCantara Back To Top

Excellent note. One question: how can I find documentation on the function LOGINPROPERTY in the Server Management Studio (SQL 2005)?

LOGINPROPERTY doesn't exist in the help index.

Have a good day.


Recommended Reading

Enabling xp_cmdshell in SQL Server

Encrypting passwords for use with Python and SQL Server

Understanding SQL Server fixed database roles

How to configure SSL encryption in SQL Server

Steps to Drop an Orphan SQL Server User when it owns a Schema or Role

get free sql tips
agree to terms

Learn more about SQL Server tools