How to unlock sql server login without changing the password

Issue : sql server login account got locked due to that production jobs are started to fail so user created ticket to unlock the login without changing the password.

Error output
===========        ========        =======
Error: SQLSTATE = 42000 Microsoft OLE DB Provider for SQL Server
Login failed for user ‘test’ because the account is currently locked out. The system administrator can unlock it. 

Work around

Lets unlock the sql login using sql server management studio 

First step is connect to server using management studio–> click on security–> Login –> right click on test login –> select properties –> go to status
You will see login locked out option is checked that mean you sql login is locked and nobody can login using this account. 

Here if you see its simple just uncheck the login is locked out option

Now click on ok to apply the change done … Ohhhhh it is asking you to reset the password to unlock the sql login but you don’t have password and don’t want to reset password as per requested by user. 
 

Here trick is you uncheck the password policy to disable the policy on temporary basis after unlocking the login you can enforce password policy again 
So uncheck the option below and click ok to apply.


Now if you want see whether sql login is unlock or not that you can do by checking the status.

You can also use alter command to disable the policy 

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

This command will unlock the sql login with new password 

ALTER LOGIN test WITH PASSWORD = ‘yourpass’ UNLOCK ;
GO

Leave a Reply

  

  

  


2 + = 5