How to Unlock a SQL Login Without Resetting the Password

By:   |   Updated: 2012-08-28   |   Comments (22)   |   Related: More > Security


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.


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 Updated: 2012-08-28

get scripts

next tip button

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

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, May 22, 2015 - 2:47:09 PM - K. Brian Kelley Back To Top

In every case I've seen this situation, it's been a third party. If we're talking about a homegrown application in my organization, unless the server sits in the DMZ, the application is using a Windows user account. :-)


Friday, May 22, 2015 - 11:17:56 AM - Daniel Decasse Back To Top

In the end the account needs to be reactivated but even in your case you use bad practice such as hard coded , probably plain text credentials. 

I appreciate the advice. I am sure I have had to use it at some point but at least highlight the concerns. 

Thursday, May 21, 2015 - 3:01:16 PM - K. Brian Kelley Back To Top


  I disagree. Let me give you a situation that comes up regularly in enterprises where you can have an account lockout and nothing intentionally malicious happened. 

  Audits are being done and the login in question is a known one because of an application. The password is hardcoded (it still happens in 2015) by the application, so the DBA can't change it. If he or she does, the application can no  longer connect because you have no option of changing the password in the application. Unfortunately, whoever doing the audit accidentally toggled the brute force switch and before anyone knew what happened, the login was locked out. 

  Given this scenario, which still happens all too frequently, how would you propose to unlock the SQL Server login and restore access to the application?

Thursday, May 21, 2015 - 2:22:06 PM - Daniel Decasse Back To Top

Changing a security setting such as enforce password policy is not a proper way to unlock an account. Knowing what the policy is and changing the password to fit the policy is the proper way to resolve this issue. if you are going to have a password policy you should use it . 

Tuesday, April 21, 2015 - 9:12:29 AM - George Back To Top

The only option for me was using the third party software SQL Server Password Changer. I believe it is the best option for unlocking the SA account.

Tuesday, September 16, 2014 - 1:01:27 PM - kmote Back To Top

+1 Perfect! Just what I needed. And at the top of my first Google search, no less. That's some service!

Monday, September 15, 2014 - 10:47:08 AM - K. Brian Kelley Back To Top

SQL Server 7.0 and 2000 do not support password lock for SQL Server logins. Therefore, there's nothing to unlock.


If it's a Windows login, the unlock must happen at the server (if it's a local account) or at Active Directory.


Monday, September 15, 2014 - 9:50:43 AM - sreenivasulus Back To Top

how to reset user password in sql server 7.0 version because the password is locked,, anyone have any idea please reply ASAP

Wednesday, August 13, 2014 - 7:19:25 AM - chandra Back To Top

sql login is automaticaly locked is there any reasons.


Wednesday, April 30, 2014 - 8:23:53 PM - Brenda Back To Top

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.

Thursday, August 30, 2012 - 8:33:43 AM - K. Brian Kelley Back To Top

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


Thursday, August 30, 2012 - 3:17:28 AM - Tlhogi Back To Top


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


Wednesday, August 29, 2012 - 7:46:26 PM - Pavan Back To Top


Wednesday, August 29, 2012 - 2:56:59 PM - K. Brian Kelley Back To Top

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 - 9:35:52 AM - Tlhogi Back To Top


What is the procedure for the sa login?

Wednesday, August 29, 2012 - 6:18:49 AM - sreekanthan Back To Top

oops!! my mistake

Tuesday, August 28, 2012 - 4:04:26 PM - Sherbaz Mohamed C P Back To Top

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

Tuesday, August 28, 2012 - 1:29:48 PM - K. Brian Kelley Back To Top

Tim, Darrell was responding to sreekanthan. :-)

Tuesday, August 28, 2012 - 11:58:17 AM - Tim Edwards Back To Top


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.


Tuesday, August 28, 2012 - 9:36:47 AM - Darrell George Back To Top

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 - 7:21:41 AM - sreekanthan Back To Top


Tuesday, August 28, 2012 - 7:20:07 AM - sreekanthan Back To Top


We can unlock by using below command without changing the password

use msdb



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