solving sql server problems for millions of dbas and developers since 2006

Identify SQL Server 2005 Standard Login Settings

By: Jeremy Kadlec | Read Comments (2) | Print

Jeremy is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com and SQL Server MVP since 2009.

Related Tips: More

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.