Unlock SQL Login Without Changing the Password

You can unlock SQL login accounts without changing any password: switch CHECK_POLICY off, then on. The login keeps its password, its permissions and its SID. Nobody has to learn anything new.

Why a SQL Login Gets Locked

A SQL login locks when someone types a wrong password too many times. The limit does not come from SQL Server. It comes from the Windows password policy of the computer that hosts SQL Server. It applies only to logins with CHECK_POLICY on. On the demo server the policy allows 10 bad attempts and then locks the login for 10 minutes.

The demo creates one login for this post. CHECK_EXPIRATION is off, so the password never expires. Replace the sample password with a password of your own, and use the same one in the sign-in line later. Run the scripts on a test server only.

IF SUSER_ID(N'UnlockDemoLogin') IS NOT NULL DROP LOGIN UnlockDemoLogin;
CREATE LOGIN UnlockDemoLogin WITH PASSWORD = N'ReplaceWith-Tea-2026x', CHECK_POLICY = ON, CHECK_EXPIRATION = OFF;

A lockout needs real sign-in attempts, so this step runs in PowerShell, not in T-SQL. The loop sends a wrong password 11 times to the demo login and to no other login. Change the server name to yours. If your Windows policy has no lockout threshold, the login never locks and the later steps show no change.

1..11 | ForEach-Object { sqlcmd -S .\SQLDEV -C -U UnlockDemoLogin -P WrongPassword1 -Q "SELECT 1" -l 5 2>&1 | Out-Null }

Now check the login. LOGINPROPERTY returns IsLocked and BadPasswordCount for a SQL login.

SELECT name,
       LOGINPROPERTY(name, N'IsLocked') AS IsLocked,
       LOGINPROPERTY(name, N'BadPasswordCount') AS BadPasswords,
       is_policy_checked AS PolicyChecked
FROM sys.sql_logins
WHERE name = N'UnlockDemoLogin';

The login is locked after 10 bad passwords. To see every locked login on a server, filter on the same property. Run this before you unlock SQL login accounts one by one, so you know how many there are.

SELECT name, LOGINPROPERTY(name, N'BadPasswordCount') AS BadPasswords
FROM sys.sql_logins
WHERE LOGINPROPERTY(name, N'IsLocked') = 1;

The client only sees Login failed for user. The error log holds the real reason. It writes the lock-out line only when a sign-in with the right password is turned away. So the real user signs in now, with the right password, and is refused. That sign-in also drops BadPasswordCount back to 0, while IsLocked stays 1. This is a throwaway demo login, so a password on the command line is fine here.

sqlcmd -S .\SQLDEV -C -U UnlockDemoLogin -P "ReplaceWith-Tea-2026x" -Q "SELECT 1" -l 5 2>&1 | Out-Null

Now read the error log.

CREATE TABLE #Log (LogDate datetime, ProcessInfo nvarchar(50), LogText nvarchar(max));
INSERT INTO #Log EXEC sys.sp_readerrorlog 0, 1, N'UnlockDemoLogin', N'locked out';
SELECT TOP (1) LogText FROM #Log ORDER BY LogDate DESC;
DROP TABLE #Log;

Wrong passwords are logged as Password did not match, and those lines name the machine that sends them. That tells you who to call.

You can also wait. The Windows policy lifts the lock by itself after the lockout duration, 10 minutes here. Waiting does not help a user who is stuck now, and it does not stop the cause.

Unlock SQL Login With CHECK_POLICY

Turn the policy off, then on again. In this test the toggle reset the bad password count and cleared the lock. Both statements sit in one batch, so the policy stays off for the shortest possible time.

ALTER LOGIN UnlockDemoLogin WITH CHECK_POLICY = OFF;
ALTER LOGIN UnlockDemoLogin WITH CHECK_POLICY = ON;

Run the check again. The lock is gone, and the policy is back on.

SELECT name,
       LOGINPROPERTY(name, N'IsLocked') AS IsLocked,
       LOGINPROPERTY(name, N'BadPasswordCount') AS BadPasswords,
       is_policy_checked AS PolicyChecked
FROM sys.sql_logins
WHERE name = N'UnlockDemoLogin';

Now sign in with the old password. It works, because the toggle never touched the password. Use the same password as before. This style puts the password on the command line, so keep it for a throwaway demo login.

sqlcmd -S .\SQLDEV -C -U UnlockDemoLogin -P "ReplaceWith-Tea-2026x" -Q "SELECT SUSER_NAME() AS SignedInAs"

You could argue that ALTER LOGIN with a new password and UNLOCK is the proper way. It is a documented option. It fits when you need a new password anyway. With a new password it has a cost. The owner must learn it. Every application that stores the old one fails, and it can lock the login again. If you know the current password, you can pass it again with UNLOCK, and nobody has to learn anything new. An administrator rarely knows it, which is why the toggle is useful.

When the Toggle Fails

A login that must change its password at the next sign-in cannot switch CHECK_POLICY off. The script below creates such a login and tries.

IF SUSER_ID(N'UnlockMustChangeLogin') IS NOT NULL DROP LOGIN UnlockMustChangeLogin;
CREATE LOGIN UnlockMustChangeLogin WITH PASSWORD = N'ReplaceWith-Tea-2026x' MUST_CHANGE, CHECK_EXPIRATION = ON, CHECK_POLICY = ON;
ALTER LOGIN UnlockMustChangeLogin WITH CHECK_POLICY = OFF;

SQL Server refuses with this message.

Msg 15128, Level 16, State 1, Line 3
The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON.

For these logins, give a new password with UNLOCK. Add MUST_CHANGE again if the user should choose a password of their own. Both statements ran without an error, and the login ended with the must-change flag on.

ALTER LOGIN UnlockMustChangeLogin WITH PASSWORD = N'ReplaceWith-Tea-2027z' UNLOCK;
ALTER LOGIN UnlockMustChangeLogin WITH PASSWORD = N'ReplaceWith-Tea-2027z' MUST_CHANGE, CHECK_EXPIRATION = ON, CHECK_POLICY = ON;

Recreating the login is the last resort. A new login gets a new SID, so its database users become orphaned until you repair them. The post Login vs User in SQL Server: Who Gets In, Who Can Do What explains why.

Stop the Next Lockout

One application with an old password can start a lockout. A connection pool can repeat the bad password many times a minute. Read the failed lines in the error log. The CLIENT value on each line points to the machine that sends them. Fix the setting there, or the login locks again.

This tip is for SQL logins only. Windows logins follow the domain policy, so an administrator unlocks them in Active Directory, not with ALTER LOGIN.

What to Remember

Switch CHECK_POLICY off and on to unlock SQL login accounts that keep their password. Use UNLOCK with a new password when the login must change it. Then find the application or person that keeps sending the old password, or the lock returns.

Do not leave CHECK_POLICY off to avoid lockouts. The policy protects the login against password guessing. When you finish with the demo, remove both logins.

DROP LOGIN UnlockMustChangeLogin;
DROP LOGIN UnlockDemoLogin;

A locked login is not a lost login, it is a policy that needs a reset.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.