Catch 22: SQL 2005 and the "MUST_CHANGE" policy

Gotcha! It's really is a catch 22 situation, and it's damn annoying. DB User X has a password set to Y, and when created that user was created with the default settings - thus automatically checking the little attribute on the user telling the server that the user has to change the password at first login. Let's say you're migrating a solution/db to SQL 2005. In this case you probably don't wanna change the password since this user, along with the associated password, is likely used in an application somewhere somehow.. so, what do you do? you go to the server, find the user in the management studio, and you try to edit it since this user should not have a password policy at all, we know the username and password should be what is set. well, sorry, but if this happened to you then you probably noticed that if you try to uncheck the "Enforce password policy" and/or "Enforce password expiration" and click "ok" you'll be hit with an error saying the changes can't be made due to the "MUST_CHANGE" flag is set on the user.. This really annoyed me a lot, so here's a little workaroud that might help you:

• Open the management studio and connect to the server with sufficient rights
• start a blank query
• run "ALTER LOGIN X WITH PASSWORD = 'Y' UNLOCK" (replace X & Y with username and password of course)

By following the above steps you should now be able to go back to the user and uncheck the checkboxes without any trouble.

To make the changes without using the management studio you can run the following in a query..

To change the password and keep the MUST_CHANGE flag: ALTER LOGIN X WITH PASSWORD = 'Y' UNLOCK MUST_CHANGE

To uncheck the checkboxes for expiration and/or policy (change "OFF" to "ON" to check): ALTER LOGIN X WITH CHECK_EXPIRATION = OFF ALTER LOGIN X WITH CHECK_POLICY = OFF

P.