When creating logins in SQL Server to allow users to connect to databases, a couple of valuable options can improve the user experience – DEFAULT_DATABASE and MUST_CHANGE. In this post, we’ll look at how these two options can streamline things for users when they connect and change passwords. Let us discuss the login experience.
The CREATE LOGIN Statement
Logins are created in SQL Server using the CREATE LOGIN statement. This statement lets you specify the login type, password, and other options. Here is the basic syntax:
CREATE LOGIN login_name WITH PASSWORD = 'password123'
To set a default database and require password changes, we can use the DEFAULT_DATABASE and MUST_CHANGE options:
CREATE LOGIN joe WITH PASSWORD = 'temp123', DEFAULT_DATABASE = accounting, MUST_CHANGE = ON
Setting a Default Database
The DEFAULT_DATABASE option sets the default database that will be selected automatically when the user first connects.
For example, if I set DEFAULT_DATABASE to ‘sales’ for my login ‘john’, whenever John connects, his default database will be charged to the sales database without him needing to run a USE statement.
This avoids an extra step for users primarily working within a single database. The default persists until explicitly changed by the user.
Requiring Password Changes
The MUST_CHANGE option, if enabled, will force the user to change their password the first time they connect after the login is created.
For example, if I specify MUST_CHANGE = ON for my login ‘joe’, Joe will be prompted to change his password before he can do anything else on the first login.
This ensures passwords are changed from any default set during login creation. MUST_CHANGE should always be paired with proper password expiration policies.
Benefits of Setting Defaults
There are a few valuable cases where these login options come in handy:
- MUST_CHANGE guarantees a password is changed from defaults at first login. This prevents leftover default passwords.
- DEFAULT_DATABASE avoids an extra USE statement to switch databases for each user.
- It can simplify account administration when users primarily use a single database.
- When combined with contained users, it provides a smoother cross-database experience.
The specific defaults that make sense will depend on your users and environment. But when used properly, they can remove minor friction points during the login process.
Checking Defaults on Logins
To check the current default database and MUST_CHANGE settings, query the sys.sql_logins system view:
SELECT name, default_database_name, must_change_password FROM sys.sql_logins
The default database and MUST_CHANGE settings are handy options to improve user experience when logging in. Use them when appropriate for your users and databases. Let me know if there is anything else you would like to know about your login experience. You can also follow me on X (twitter).
Reference: Pinal Dave (https://blog.sqlauthority.com)
