Aug27

Written by:David Aldridge
8/27/2010 12:36 PM 

SQL 2005 and 2008 added password expiration and forced password change to SQL logins.  Trouble with this is that if you try to set password expiry off when MUST CHANGE is set,  you get the message from the title of this post.

By default when you create a SQL login, it has MUST_CHANGE enabled.  This will prevent it from being used to access databases until the password is changed.  This is decidedly annoying when you are transferring logins and sites.

To remove MUST_CHANGE and password expiry, you need to use some TSQL code:

ALTER LOGIN MyLogin WITH PASSWORD = 'newpassword';
ALTER LOGIN MyLogin CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF;

Tags:

1 comment(s) so far...

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

May not work if you get this error - Msg 15128, Level 16, State 1, Line 2
The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON.

By BOB on  4/12/2011 3:53 PM

Your name:
Your email:
(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Security Code
Enter the code shown above in the box below
Add Comment  Cancel 
You must be logged in and have permission to create or edit a blog.