Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

How to Unlock a SQL Login Without Resetting the Password

MSSQLTips author K. Brian Kelley By:   |   Read Comments (13)   |   Related Tips: More > Security
Problem

I have a SQL Server login that's been locked out. I went to unlock it, but it's telling me I have to change the password to do so. I can't change the password and I don't know what it is. How can I unlock the account without changing the password?  Check out this tip to learn more.

Solution

Starting in SQL Server 2005, SQL Server can use the password policies that the operating system uses. This includes account lockout. If a SQL Server login is configured to use password policy enforcement and your organization uses account lockout after a certain number of failed logins, you can end up locking out a SQL Server login via the same scenario.

SQL Server Management Studio Login Properties for Enforce password policy

This can result in the account being locked out. Unlocking the account is easy to do if you want to reset the password. If you don't however, SQL Server won't let you directly. For instance, simply unchecking the box beside Login is locked out and clicking OK won't work.

SSMS Login Properties - Login is locked out

Instead, you'll get the following error:

SSMS error - Reset password for the login while unlocking. (SqlManagerUI)

The trick is to temporarily take the login out of password policy enforcement by unchecking it, as shown in Figure 4.

SQL Server Management Studio Enforce Password Policy

This will disable the unlock checkbox.

SSMS SQL Server Authentication login is locked out is unchecked

Click OK to confirm the change and the SQL Server login will unlock. This will permit connections via that login again. If there is a need for the password enforcement to be turned back on, you can do so and it won't re-lock the login. However, in either case be sure you know what caused the lockout in the first place. The reason for having account lockout is to prevent a security breach due to someone brute forcing the password for an account. Disabling password policy enforcement basically means an attacker can try to guess the password.

Next Steps


Last Update: 8/28/2012


About the author
MSSQLTips author K. Brian Kelley
K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Tuesday, August 28, 2012 - 7:20:07 AM - sreekanthan Read The Tip

Hi,

We can unlock by using below command without changing the password

use msdb

ALTER LOGINWITH PASSWORD = '' UNLOCK


Tuesday, August 28, 2012 - 7:21:41 AM - sreekanthan Read The Tip

ALTER LOGIN <Login> WITH PASSWORD = '<Password>' UNLOCK


Tuesday, August 28, 2012 - 9:36:47 AM - Darrell George Read The Tip

The individual who posted the question mentioned that they didn't have the ability to change the password, because they didn't know it. The two above solutions/examples utilize parameters that require the password to be entered. How is this solution going to solve the problem?       


Tuesday, August 28, 2012 - 11:58:17 AM - Tim Edwards Read The Tip

Darrell,

The solutions provided by Brian shouldn't require knowing the password for the Login, they just require the assistance of an administrator who is in either the Security Administrator or System Administrator server-level roles to effect the change.  At no point did Brian have to login as the user whose account was locked out to change the settings around password enforcement in order to unlock the user's account. He just needs to go into the properties of the Login in SSMS to make the change.

Tim


Tuesday, August 28, 2012 - 1:29:48 PM - K. Brian Kelley Read The Tip

Tim, Darrell was responding to sreekanthan. :-)


Tuesday, August 28, 2012 - 4:04:26 PM - Sherbaz Mohamed C P Read The Tip

Furthur more to this, If you wanna find the lost SQL login password using trial and error methord, here is how. I have explained in my blog at http://www.sherbaz.com/2011/10/check-sql-authentication-login-account-password/


Wednesday, August 29, 2012 - 6:18:49 AM - sreekanthan Read The Tip

oops!! my mistake


Wednesday, August 29, 2012 - 9:35:52 AM - Tlhogi Read The Tip

 

What is the procedure for the sa login?


Wednesday, August 29, 2012 - 2:56:59 PM - K. Brian Kelley Read The Tip

Tlohgi, I'm afraid I don't understand your question. When you ask what the procedure is, can you give a frame of reference?


Wednesday, August 29, 2012 - 7:46:26 PM - Pavan Read The Tip

ALTER LOGIN sa WITH CHECK_POLICY = OFF;
ALTER LOGIN sa WITH CHECK_POLICY = ON
;
GO


Thursday, August 30, 2012 - 3:17:28 AM - Tlhogi Read The Tip

 

Hi K. Brian Kelly, not "Stored Procedure" but how do you unlock the password for the sa account??  

 


Thursday, August 30, 2012 - 8:33:43 AM - K. Brian Kelley Read The Tip

Tlogi. The technique in this tip works on any login. Also, the T-SQL Pavan posted should work as well.

 


Wednesday, April 30, 2014 - 8:23:53 PM - Brenda Read The Tip

Great tutorial! Unfortunately I am locked out of SA account and the Windows Authentication is also greyed out. Now I have successfully unlocked the account using a third party software - SQL Server Password Changer.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.