Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Identify SQL Server 2005 Standard Login Settings


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

Problem
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.

Solution
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

LOGINPROPERTY Code Snippets

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');
GO
 

Locked out standard login

SELECT LOGINPROPERTY('sa', 'IsLocked');
GO
 
Result Set Legend
  • 0 - Login is not locked out
  • 1 - Login is locked out

Expired password

SELECT LOGINPROPERTY('sa', 'IsExpired');
GO
 
Result Set Legend
  • 0 - Password is not expired
  • 1 - Password is expired

Must change password at next login

SELECT LOGINPROPERTY('sa', 'IsMustChange');
GO
 
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');
GO
 
Time of the last failed login attempt
SELECT LOGINPROPERTY('sa', 'BadPasswordTime');
GO
 

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

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

Date when the login was locked out

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

Password hash

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

LOGINPROPERTY Special Notes

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 MSSQLTips.com.


Last Updated: 2008-04-16


get scripts

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips
Related Resources




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 (*).

*Name    *Email    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?

 http://msdn.microsoft.com/en-us/library/ms345412(SQL.90).aspx


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.


Learn more about SQL Server tools