Error: 18486, Severity: 14, State: 1

SQL Server 2005/2008: 
How to find locked out user and unlock them?
How to unlock a login without changing password?

Login failed for user 'xxx' because the account is currently locked out. The system administrator can unlock it.  [CLIENT: <local machine>]
Error: 18486, Severity: 14, State: 1

Today I had a client who asked me how to unlock a SQL Server Login. So firstly I listed out all the logins that were locked out using the following query.

select name, 'Is Locked' from sys.server_principals  WHERE LOGINPROPERTY (name , 'IsLocked') = 1

However, when I tried to unlock the password I received the following error. 

'Reset password for the login while unlocking'

The normal process to unlock the login would be change password and also force the user to change the password on first login. This can be done using the following TSQL.


ALTER LOGIN [yourloginname] WITH PASSWORD=N'xxxxx' UNLOCK MUST_CHANGE

Instead of this you can do the following too to unlock the login but still maintain the password.

USE [master]

ALTER LOGIN [yourloginname] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

ALTER LOGIN [yourloginname] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=ON, CHECK_POLICY=ON

GO

Just by disabling and then enabling the password check policy, you can unlock the account.