Creating a SQL Server authentication login with a passwordThe following example creates the login
CREATE LOGIN Mary7 WITH PASSWORD = 'A2c3456$#' ;
Using Options to Create with expiration,policyThe following example creates the login
Mary8with 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;
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;