Click on the banner to learn about and purchase my database training on Azure

SQL Server - How to unlock a login without having to change/reset the password

Views: 911 views
Reading Time: 3 minutes

Speak guys!
In this article I wanted to share with you a simple solution to unlock a user without having to change/reset the password, which can sometimes be very annoying when having to change a user's password so that he logs back into the bank.

To learn more about Policies, Automatic Blocking and Expiration, read the article SQL Server - Password Policies, Password Expiration, Mandatory Password Change, and Login Lockout after N attempts.

If you liked this content and want to delve deeper into the data security part, be sure to check out my course Security in SQL Server

When you create a login using a password policy (CHECK_POLICY) to ensure that passwords are complex enough and that short and weak passwords are not accepted, depending on your Windows/AD settings, the auto-lock feature after N incorrect attempts may be activated.

To test this scenario, we will create a user with password policy enabled:

Now I ensure that the auto-blocking properties are enabled on the “Local Security Policy” screen (secpol.msc)

We will block this user trying to connect me more than 5 times (parameter I set in the previous screen) with the wrong password

To check if the user is blocked, you can use the query below:


I can also check if SQL Login is blocked by SSMS interface

Now comes the tip of this post. How can I unlock this login without having to change the password?

If you try to simply uncheck this option in the SSMS interface, you will see this error message:

For this to work, uncheck the option of “Enforce password policy”

Ready! The user has already been unlocked. You can now check this option again, if you want to keep it the way it was before.

You can also use this SQL command to unlock the user without having to change the password:

And that's it!
Short and quick post with this useful everyday tip.
Now you won't need to change an application's user password because a dev set up the wrong environment 🙂