How to Create Login? How to Turn OFF Expiration and Policy Check on login? Steps to unlock and Locked in Login in SQL Server

Creating a SQL Server authentication login with a password

The following example creates the login Mary7 with password A2c3456.

Transact-SQL

CREATE LOGIN Mary7 WITH PASSWORD = 'A2c3456$#' ;  

Using Options to Create with expiration,policy

The following example creates the login Mary8 with password some of the optional arguments.

CREATE LOGIN Mary8 WITH PASSWORD = 'A2c3456$#' MUST_CHANGE,  
CHECK_EXPIRATION = ON,  
CHECK_POLICY = ON; 

ALTER LOGIN to TURN OFF Expiratio,Policy

ALTER LOGIN Mary8 WITH PASSWORD = 'A2c3456$#',  
CHECK_EXPIRATION = OFF,  
CHECK_POLICY = OFF;  

Remarks

Passwords are case-sensitive.
Prehashing of passwords is supported only when you are creating SQL Server logins.

If MUST_CHANGE is specified, CHECK_EXPIRATION and CHECK_POLICY must be set to ON. Otherwise, the statement will fail.

A combination of CHECK_POLICY = OFF and CHECK_EXPIRATION = ON is not supported.

When CHECK_POLICY is set to OFF, lockout_time is reset and CHECK_EXPIRATION is set to OFF.


 How to unlock user account in SQL Server?

 if we know password

ALTER LOGIN foo WITH PASSWORD = '<enterStrongPasswordHere>' UNLOCK

 if we did nt know password then

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