Unlock SQL User without changing the password

Recently one of the junior DBAs was finding it difficult unlock a SQL Server authenticated user on SQL Server 2005. The user had typed the wrong password several times and the account got locked.

When trying to unlock the user from the GUI, SQL Server prompted that the password also be changed. This is because the CHECK_POLICY option was set to ON.

But the requirement was just to unlock the account. The issue was resolved with the following workaround.

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

This can also be achieved using the GUI. The highlighted option needs to be unchecked and checked again after clicking Ok.

This way the sql user account was unlocked without changing the password. The only downside to this approach is that the when the CHECK_POLICY is turned OFF, the password history for that account is cleared and the value of lockout_time is also reset. The article on ALTER LOGIN command on MSDN has more details.