Alter login in SQL Server

Alter login statements can be used to changes the properties of a SQL Server login account.

Syntax:

ALTER LOGIN loginname;
GO

Enable a disabled login –

Syntax :

ALTER LOGIN loginname ENABLE;

Example –

ALTER LOGIN geeks ENABLE;

Change password of a login
Syntax :

ALTER LOGIN geeks WITH PASSWORD = 'newpassword';

Example –

ALTER LOGIN geeks WITH PASSWORD = ‘G9h$fm@1LZe’;

Change the password for a login when logged in using the same login

Syntax:

ALTER LOGIN loginname 
WITH PASSWORD = 'newpassword' OLD_PASSWORD = 'oldpassword';

Example –

ALTER LOGIN geeks 
WITH PASSWORD = ‘G9h$fm@1LZe’ OLD_PASSWORD = ‘Ge@@k$s12’;

Change name of a login

Syntax:

ALTER LOGIN loginnmae WITH NAME = newname;

Example –

ALTER LOGIN geeks WITH NAME = geek;

Unlock a login

Syntax:

ALTER LOGIN loginnmae WITH PASSWORD = 'password' UNLOCK ;

Example –

ALTER LOGIN geeks WITH PASSWORD = ‘G9h$fm@1LZe’ UNLOCK ;

Unlock a login without changing the password

Syntax:

ALTER LOGIN [loginname] WITH CHECK_POLICY = OFF;
ALTER LOGIN [loginnmae] WITH CHECK_POLICY = ON;

Example –

ALTER LOGIN geeks WITH CHECK_POLICY = OFF;

ALTER LOGIN geeks WITH CHECK_POLICY = ON;