Set Never Expiration Password and Change Password for Login on SQL Server

Fix a SQL Server Login which has MUST_CHANGE set to ON

This is one of those nasty little thorns that sticks you every once in a while.

You add a new SQL Server authentication login with Server Manager and you forget to uncheck the boxes for User must change password, Enforce password, and Enforce Expiration. Being this is an application user for which you don’t want to enforce password restrictions and which uses ODBC, therefore never seeing a login dialog, they never get an opportunity tochange the password. Consequently the login can’t be used as is.

Ok, you realize your mistake too late. The login has been created. It has been associated with one or more databases and/or schemas. Permissions have been granted, etc., etc., etc. How do you fix it? Guess what? If you try to just want to uncheck the “User must change” check box, you can’t! It is greyed out.

This conundrum perplexed me to no end when first encountered. Searching the internet turned up several solutions like this one:
ALTER LOGIN test_must_change WITH PASSWORD = ‘newpass’,
      CHECK_POLICY = OFF,
      CHECK_EXPIRATION = OFF;

Guess what happens (you have three choices and the first two don’t count). You get this error message
Msg 15128, Level 16, State 1, Line 1
The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON.

If you try to user Server Manager and just uncheck the ‘Enforce password policy’ or ‘Enforce password expiration’ boxes, you get this

THE FIX

You MUST change the password first and it must be done without changing the other password policy options. You CAN change the password to be the same as the existing one. However, you do it, you still must effect a change on the password an then change the policy options as a separate step. If you choose to use the Server Manager wizard, when you change the password, the ‘User must change password’ check box will become available and you can then uncheck it (do not uncheck the other policy check boxes if they already were). You can then save the change, open the wizard again, uncheck the other policy restrictions, and save it again.

If you decide to use a query, you can use code similar to this
USE Master
GO
ALTER LOGIN test_must_change WITH PASSWORD = ‘samepassword’
GO
ALTER LOGIN test_must_change WITH
      CHECK_POLICY = OFF,
      CHECK_EXPIRATION = OFF;

SQL SERVER: CHANGE A PASSWORD IN SQL SERVER

Question: How do I change the password for a user/login in SQL Server?

Answer: In SQL Server, the password is associated with the SQL Server Login. The Login is then mapped to the database user. So to change a password in SQL Server, you need to execute the ALTER LOGIN statement.

SYNTAX

The syntax for changing a password in SQL Server (Transact-SQL) using the ALTER LOGIN statement is:

ALTER LOGIN login_name
WITH PASSWORD = 'password' | hashed_password HASHED
         [ OLD_PASSWORD = 'old_password' ]
         | MUST_CHANGE
         | UNLOCK
[ CHECK_EXPIRATION = ON ];

Parameters or Arguments

login_name

The Login whose password you wish to change. This Login will the one associated with the database user whose password you wish to change.

password

The new password to assign.

hashed_password

The new hashed value of the password to assign to the Login.

old_password

The old password.

MUST_CHANGE

It is used when you want to force the password to be changed the first time that the Login is used after the ALTER LOGIN statement.

UNLOCK

It will unlock a Login that has been locked out.

CHECK_EXPIRATION

It is by default set to OFF. This option determines whether password expiration policy is enforced. You must specifiyCHECK_EXPIRATION = ON when you use the MUST_CHANGE option.

EXAMPLE - CHANGE PASSWORD

Let's look at how to change a password using the ALTER LOGIN statement in SQL Server (Transact-SQL).

For example:

ALTER LOGIN techonthenet
WITH PASSWORD = 'bestsite';

This ALTER LOGIN example would alter the Login called techonthenet and change the password of this login to 'bestsite'.

EXAMPLE - CHANGE PASSWORD AND FORCE CHANGE

Let's look at how to change a password and force the password to be changed after the first login using the ALTER LOGIN statement in SQL Server (Transact-SQL).

For example:

ALTER LOGIN techonthenet
WITH PASSWORD = 'bestsite' MUST_CHANGE, 
CHECK_EXPIRATION = ON;

This ALTER LOGIN example would alter the Login called techonthenet and change the password of this login to 'bestsite'. But because we have specified the MUST CHANGE option and set the CHECK_EXPIRATION to ON, the password will have to be changed again in SQL Server after the first login (following the ALTER LOGIN statement). So in effect, it is like resetting a password to a temporary password for a Login.