Creating a secure SQL server login - CHECK_EXPIRATION & CHECK_POLICY
- by cabhilash
In SQL Server you can create users using T-SQL or using the options provided by SQL Server Management Studio.
CREATE LOGIN sql_user WITH PASSWORD ='sql_user_password' MUST_CHANGE,
DEFAULT_DATABASE = defDB,
CHECK_EXPIRATION = ON,
CHECK_POLICY = ONAs mentioned in the previous article (http://weblogs.asp.net/cabhilash/archive/2010/04/07/login-failed-for-user-sa-because-the-account-is-currently-locked-out-the-system-administrator-can-unlock-it.aspx) when CHECK_POLICY = ON user account follows the password rules provided in the system on which the SQL server is installed.
When MUST_CHANGE keyword is used user is forced to change the password when he/she tries to login for the first time.
CHECK_EXPIRATION and CHECK_POLICY are only enforced on Windows Server 2003 and later.
If you want to turn off the password expiration enforcement or security policy enforcement, you can do by using the following statements. (But these wont work if you have created your login with MUST_CHANGE and user didn't change the default password)
ALTER LOGIN sql_login WITH CHECK_EXPIRATION = OFF
go
ALTER LOGIN sql_login WITH CHECK_POLICY = OFF
