When a user account is created on SQLServer on 2005 with user account set to “Enforce password policy” and “Enforce password expiration”, once set this policy and expiration can’t be changed using.
ALTER LOGIN test WITH CHECK_EXPIRATION = OFF;
Or
ALTER LOGIN test WITH CHECK_POLICY = OFF;
SQLserver will return the following 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.
To fix this issue which will keep the password the same and it will turn off check policy and expiration.
ALTER LOGIN test WITH PASSWORD = ”
GO
ALTER LOGIN test WITH
CHECK_POLICY = OFF,
CHECK_EXPIRATION = OFF;