Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Thursday, 16 June 2016

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.
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;