SQL SERVER – Login Experience with DEFAULT_DATABASE and MUST_CHANGE

Two small options on CREATE LOGIN decide the whole login experience. One sends the user straight to the right database, the other makes them pick a new password on first connect. Both are worth using. Put them together carelessly and you lock the person out so thoroughly that they cannot even change the password to get back in. I ran all of this on SQL Server 2025 to be sure.

Start With the Syntax, Because It Is Not What You Expect

Every other option in CREATE LOGIN takes an equals sign, so people write MUST_CHANGE the same way. It does not work.

CREATE LOGIN joe WITH PASSWORD = '<temporary password>',
    DEFAULT_DATABASE = BlogLab,
    MUST_CHANGE = ON;

Msg 102, Level 15, State 1
Incorrect syntax near 'MUST_CHANGE'.

MUST_CHANGE is not an option in the list. It is a bare keyword that belongs to the password, so it sits immediately after the password and before the comma. Move it and you get a different complaint.

CREATE LOGIN joe WITH PASSWORD = '<temporary password>' MUST_CHANGE,
    DEFAULT_DATABASE = BlogLab;

Msg 15099, Level 16, State 1
The MUST_CHANGE option cannot be used when CHECK_EXPIRATION is OFF.

That one is fair. Forcing a change on first use is an expiry rule, so the expiry machinery has to be switched on. This is the version that runs.

CREATE LOGIN joe WITH PASSWORD = '<temporary password>' MUST_CHANGE,
    DEFAULT_DATABASE = BlogLab,
    CHECK_EXPIRATION = ON,
    CHECK_POLICY = ON;

What the User Sees

Joe connects with the temporary password and is turned away, politely and clearly:

Login failed for user 'joe'.
Reason: The password of the account must be changed.

Every proper client offers a change-password box at this point. On the command line sqlcmd does it with an extra switch, where the old password goes in with -P and the new one with -Z.

sqlcmd -S .\SQLDEV -U joe -P "<old password>" -Z "<new password>" -C

DEFAULT_DATABASE Is Not a Convenience, It Is a Gate

This is the part people get wrong. DEFAULT_DATABASE sounds like a shortcut that saves a USE statement. It is more than that. If the login cannot open that database, the login does not fall back to master. It fails outright.

I made a login whose default database was BlogLab and deliberately gave it no user in BlogLab. Here is the connection attempt:

Login failed for user 'joe5'.
Cannot open user default database. Login failed.

The client is told almost nothing. The server log is the only place the real reason appears:

Error: 18456, Severity: 14, State: 40.
Login failed for user 'joe5'. Reason: Failed to open the database 'BlogLab'
specified in the login properties.

State 40 means the default database. Worth memorising, because 18456 arrives for a dozen different reasons and the state number is the only thing that separates them. I set the same database offline and got exactly the same failure, so a database that is merely unavailable does it too, not only a missing user.

The Trap, and It Is a Real One

Now put the two together. A new starter gets a login with MUST_CHANGE and a default database, and somebody forgets to create the user inside that database. It happens constantly, because the two jobs are done by different statements and often by different people.

The person tries to connect and is told to change the password. Sensible enough. So they change it. I ran that exact change and watched it fail, and this is the order it lands in the log:

21:58:49  Login failed. Reason: The password of the account must be changed.
21:58:54  Login failed. Reason: Failed to open the database 'BlogLab'.
21:58:55  Login failed. Reason: Password did not match that for the login.

Read that middle line. The change is carried on a login that has to complete, and it cannot complete, because the default database is still shut. So the new password never takes. I checked afterwards and the login still reported IsMustChange as 1 with the password last set in 1900, which is what SQL Server shows when a password has never been set by its owner.

The user is now stuck in a circle. They cannot get in without changing the password, and they cannot change the password because getting in is what fails. Nothing they do from their end helps.

Two Ways Out

An administrator fixes the database side, and then the same change command works first time. I repeated it after creating the user and the password was accepted, IsMustChange went to 0, and the next connect landed in BlogLab.

The other way needs no administrator at all, and it is worth knowing when you are the one locked out at seven in the morning. Name a database in the connection yourself and the default is skipped entirely.

sqlcmd -S .\SQLDEV -U joe5 -P "<password>" -d master -C

That connects. Same login, same unreachable default database, and it lands in master without complaint. In a connection string it is the Initial Catalog, and in the SSMS connect dialogue it is under Options, Connection Properties.

Checking What Is Set

You will see this query passed around, and it does not run:

SELECT name, default_database_name, must_change_password FROM sys.sql_logins;

Msg 207, Level 16, State 1
Invalid column name 'must_change_password'.

There is no such column. The view carries is_policy_checked and is_expiration_checked, but the must-change flag lives behind LOGINPROPERTY instead. This is the one that works:

SELECT name,
       default_database_name,
       LOGINPROPERTY(name, 'IsMustChange')        AS must_change,
       LOGINPROPERTY(name, 'PasswordLastSetTime') AS password_set,
       is_policy_checked,
       is_expiration_checked
FROM sys.sql_logins
ORDER BY name;

A password_set of 1900-01-01 is your sign that a login was created and never used. On a server with a few hundred logins that column alone is worth the query.

One Thing CHECK_POLICY Does Not Promise

CHECK_POLICY = ON does not mean SQL Server has its own idea of a strong password. It hands the question to the Windows policy of the machine the instance is running on. I created a login with the password temp123 and CHECK_POLICY switched on, and it was accepted, because the local policy on my test machine asks for nothing.

On a domain-joined production server the policy is usually real and that same password would be refused. The point is that the strength of CHECK_POLICY is entirely borrowed. If you are relying on it, go and look at what the host actually enforces rather than assuming the switch is doing the work.

How I Would Use Them

Create the user in the database before you point a default database at it, or in the same script, in that order. Treat DEFAULT_DATABASE as a permission you are asserting rather than a nicety.

Leave the default at master for anyone who works across several databases, because a per-user default only helps somebody who genuinely lives in one place.

And when a new starter reports that they cannot log in, ask for the state number out of the ERRORLOG before you ask them anything else. State 40 and you already know the answer.

DEFAULT_DATABASE is not a shortcut to a database, it is a condition of entry.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.