How do I force a SQL Login to change it’s password on next login

Forcing someone to change their password on a periodic basis is a pretty common thing, and any time I’ve gotten a new active directory login I’ve been given an obviously bad password and forced to change my password on the next login. It’s a good idea right?

So can we do that with SQL Logins? (And yes, I realize by preference we should be using AD Logins, but sometimes you have to do what you have to do.) Absolutely we can. And should. I’m a firm believer that DBAs should not be in the password business. It’s your id. I don’t want to know the password. I don’t know the password. Don’t ask me. But of course when a DBA creates a SQL Login they have to enter the password, requiring them to forget that password going forward. Which is particularly hard if you had to email it to the user. (Email search)

All of that brings us back to How do we do it. The clause MUST_CHANGE after the password.

CREATE LOGIN kenneth_test WITH PASSWORD ='P@ssw0rd' MUST_CHANGE, 
     CHECK_EXPIRATION = ON;

You’ll notice a few things here. First of all I’m terrible at naming things. But more importantly CHECK_EXPIRATION must be turned on. Oh, and it’s not in the command because it’s the default, but CHECK_POLICY has to be on as well. This can cause a few difficulties over time depending on your policies. If you allow someone to have a non-expiring password (not the best idea, but again it happens in the real world) then you have to remember to turn it back off later.

ALTER LOGIN [kenneth_test] WITH CHECK_EXPIRATION = OFF;

This won’t run if they still have MUST_CHANGE turned on so you might want to know when they have changed their password. To do that we need the LOGINPROPERTY command. If you are doing a bunch at once you could use the PasswordLastSetTime property, but honestly, that’s going to be tricky. Here’s my preference:

SELECT 'ALTER LOGIN ' + quotename(name) + ' WITH CHECK_EXPIRATION = OFF'
FROM sys.server_principals
WHERE LOGINPROPERTY(name,'IsMustChange') = 0
  AND LOGINPROPERTY(name,'DaysUntilExpiration') IS NOT NULL;

If DaysUntilExpiration is non NULL then CHECK_EXPIRATION is still turned on. And IsMustChange should be pretty obvious.

Last but not least, what to expect. If you create the above login, then log in as that id you’ll see the following:

If you don’t manage to successfully change the password then you can’t log in. This is pretty cool if you are logging in using something like SQL Server Management Studio (SSMS) but I’m not sure how it will work if your first connection is through a piece of code. If you are handing out a login to be used in code I’d recommend having them log in once through SSMS (or other similar tool) and change the password before doing anything else.