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 to change 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;
