How do I unlock a SQL Server Login using T-SQL (without changing the password)?


July 22, 2021 by Kenneth Fisher

We recently had an application login (SQL Server authenticated) in one of our training environments start locking out on a regular basis. I won’t go into why other than to say we did resolve it eventually. This was a major problem that escalated rather quickly up our management chain. We needed to solve it ASAP. And because of that we needed not only a long term solution but a short term one as well. The short term solution involved creating a script that unlocked the login (if it’s currently locked) and sticking it in a job that runs every 5 minutes.

Note: This is not something you should be doing in production! This creates a major security hole.

If I could have easily made that statement blink as well as red and bold I probably would have. Do not use this in production. That said, the pieces of this script are highly useful so after much thought I decided to share. It’s not like any of it is secret after all. The script is fine, it’s just putting it in a regular job that becomes an issue.

IF LOGINPROPERTY('SQLID','islocked') = 1

The first part of the script LOGINPROPERTY is checking the islocked flag to see if the login is currently locked. The property functions are highly useful in scripts like this so remembering they exist is a good idea. Why check? I don’t want to make any changes to the login unless I have to. Also changing the CHECK_POLICY flag to off the password history is reset (among other things). Not really a huge issue in this case but something to remember.

Next, you use ALTER LOGIN to turn the check_policy flag off and then on again. This is how you unlock the login. I should point out that this is also how you would do this through the GUI. You’d uncheck the CHECK POLICY checkbox, save, go back into the properties window and re-check it.

If you want to unlock it and are ok with changing the password at the same time you can just do this:


But you are going to need a new password each time.

2 thoughts on “How do I unlock a SQL Server Login using T-SQL (without changing the password)?

  1. […] Kenneth Fisher needs bad things done fast: […]

  2. […] How do I unlock a SQL Server Login using T-SQL (without … […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: