This morning when an aggravated user contacted me that they could not get into a new database that I setup on Friday, I discovered that I had made a mistake.  Well that’s never happened before, well not today anyway. 

Alright, here we go.  I created three new logins that would access development and acceptance copies of a production database that I had just setup on an acceptance server using Red Gate‘s SQL Compare and SQL Data Compare (which I will blog about soon, excellent product).  In my possession, actually a sharepoint list, I had the password for each of these three new logins.  Today I found out that the password I had listed was out of date.  No problem, easy fix!

Using SSMS I would easily change the passwords and then update my sharepoint list to the new passwords, this is DBA 101 stuff.  Not so fast my friend!  SSMS then presented an error stating that the password did not meet our ultra-rigid fort knox password policy (see posts on security).  No problem, I forgot to uncheck the ‘Enforce password policy’ check box.  That happens all the time when I get in a hurry.  Now it is unchecked and SSMS presents the following error:

The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON.

There was my real mistake committed on Friday;  I forgot to uncheck the ‘User must change password at next login’ check box when I created the logins.  It was a rookie mistake, but I was in a hurry to get things done so I could go and get my root canal in the afternoon.  Luckily for me this was not a resume updating event.  In order to fix this we need to do the following:

USE MASTER
GO
ALTER LOGIN [userlogin] WITH PASSWORD ‘original password’
GO
ALTER LOGIN [userlogin] WITH CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF;

Then I was able to change the password, which I used ALTER LOGIN since I had it in the query window.  What a way to start the week off.  Enjoy!