powershelldba.de

SQL Server Logins: DEFAULT_DATABASE and MUST_CHANGE

Every login connects to a default database. Most DBAs set it once and forget it. But when that database is dropped, or when a contractor needs a temporary password, DEFAULT_DATABASE and MUST_CHANGE become critical. Understand them, or face login failures in production.

What Is DEFAULT_DATABASE?

When a login connects to SQL Server, it must connect to some database context. DEFAULT_DATABASE specifies which one.

Example: Login Connection Flow

1. User connects: "sqlcmd -S localhost -U appuser"
2. Authentication succeeds
3. SQL Server checks appuser's DEFAULT_DATABASE
4. If DEFAULT_DATABASE = "ProductionDB", connection is established in ProductionDB context
5. User's queries run against ProductionDB by default

-- User can switch databases manually:
USE DifferentDB;
SELECT * FROM DifferentDB.dbo.Customers;

Check a Login's Default Database

SELECT
  name,
  default_database_name
FROM sys.server_principals
WHERE type = 'S'  -- SQL Login (not Windows)
  AND name = 'app_user';

-- Result:
-- name      | default_database_name
-- app_user  | ProductionDB

Why DEFAULT_DATABASE Matters

Scenario 1: Production DB Is Dropped

-- Setup:
-- appuser's DEFAULT_DATABASE = "ProductionDB"
-- Someone drops ProductionDB for cleanup

DROP DATABASE ProductionDB;

-- What happens next?
-- User tries to connect: sqlcmd -S localhost -U appuser
-- Result: CONNECTION FAILS
-- Error: "Cannot open database 'ProductionDB' requested by login"

-- The login exists, credentials are valid, but the default DB is gone!
-- Now the DBA must:
ALTER LOGIN app_user WITH DEFAULT_DATABASE = master;
-- OR create the database again
-- OR change all connection strings in the application
⚠ Critical issue: If many logins point to a dropped database, mass login failures occur silently. Applications timeout connecting, users blame the DBA. Prevention: Always set DEFAULT_DATABASE to a stable, non-production database (like master or a utilities DB).

Scenario 2: Multi-Tenant Application

-- Scenario: SaaS app with separate DB per customer
-- customer1_user DEFAULT_DATABASE = customer1_db
-- customer2_user DEFAULT_DATABASE = customer2_db

-- When customer2_user connects, they automatically land in customer2_db
-- The application doesn't need to explicitly SELECT customer2_db (though it usually does)

-- Advantage: Reduces configuration in connection strings
-- Disadvantage: If DB is deleted, login breaks

What Is MUST_CHANGE?

MUST_CHANGE forces a user to change their password on the next successful login attempt. Commonly used for:

Create Login with MUST_CHANGE

-- Create a new login for a contractor
-- Set password and require change on next login
CREATE LOGIN contractor_user
  WITH PASSWORD = 'TempPassword123!' MUST_CHANGE,
  DEFAULT_DATABASE = master;

-- Next time contractor_user tries to log in:
-- Connection succeeds, but user is prompted to change password
-- Until password is changed, user cannot execute queries beyond ALTER LOGIN

Check MUST_CHANGE Status

SELECT
  name,
  create_date,
  modify_date
FROM sys.server_principals
WHERE type = 'S'
  AND name = 'contractor_user';

-- Note: MUST_CHANGE flag is not directly queryable
-- Instead, you can check if the password was recently set
-- (create_date ≈ today means recently created with MUST_CHANGE)

Alter Existing Login to Enforce MUST_CHANGE

-- For existing user, reset password and force change
ALTER LOGIN existing_user
  WITH PASSWORD = 'NewTempPassword456!' MUST_CHANGE;

-- Next login: User is prompted to change password
-- User cannot run queries until password is changed

User Experience: MUST_CHANGE in Action

Scenario: Contractor Logs In

-- Contractor receives credentials:
-- Login: contractor_john
-- Password: TempPassword123!
-- DEFAULT_DATABASE: master

-- Contractor runs: sqlcmd -S sqlserver.company.com -U contractor_john
-- Prompted: "Password for contractor_john:"
-- [enters TempPassword123!]
-- Connection succeeds, but prompt appears:
-- "Your password must be changed before you can access the database."
-- Prompted: "Enter new password:"
-- [enters new password]
-- New prompt: "Confirm password:"
-- [re-enters new password]
-- ✓ Password changed successfully
-- Now connected to master database, ready for queries

What If User Ignores MUST_CHANGE?

-- If SSMS is used (GUI), the password change dialog appears
-- User cannot minimize or bypass it
-- They must change password to proceed

-- Command-line (sqlcmd):
-- User is forced to run: ALTER LOGIN contractor_john WITH PASSWORD = 'new_pass';
-- Error if they try other statements: "Login failed for user 'contractor_john'. Reason: Password must be changed."

Common Pitfalls & Solutions

Pitfall 1: DEFAULT_DATABASE Points to Non-Existent DB

-- Scenario:
ALTER LOGIN appuser WITH DEFAULT_DATABASE = OldProductionDB;
DROP DATABASE OldProductionDB;

-- Result:
-- appuser can no longer connect (silent failure from app perspective)
-- Error message (if checking manually):
-- Msg 4060, Level 11: Cannot open database 'OldProductionDB'

-- Fix:
SELECT name, default_database_name
FROM sys.server_principals
WHERE type = 'S'
  AND default_database_name NOT IN (SELECT name FROM sys.databases);

-- This query finds all logins pointing to non-existent databases
-- Then alter them:
ALTER LOGIN appuser WITH DEFAULT_DATABASE = master;

Pitfall 2: MUST_CHANGE but No Way to Change Password

-- Scenario: You set MUST_CHANGE on a service account
-- But the service account is used by a batch job (no interactive login)
-- The batch job fails: "Password must be changed"
-- The service account can't change its password (no interactive shell)

-- Fix: Don't use MUST_CHANGE on service accounts
-- Instead, manually rotate their passwords on a schedule
-- Batch jobs use fixed passwords (managed by application team or vault)

-- If you must force password change:
ALTER LOGIN service_account WITH PASSWORD = 'new_pass' MUST_CHANGE;
-- Then have a human (or admin script) execute:
-- sqlcmd -S server -U service_account -P old_temp_pass
--   -Q "ALTER LOGIN service_account WITH PASSWORD = 'new_permanent_pass';"
-- This interactively changes the password, satisfying MUST_CHANGE

Pitfall 3: Forgetting DEFAULT_DATABASE When Cloning Logins

-- Scenario: Copy permissions from one login to another
-- But forget to change DEFAULT_DATABASE

CREATE LOGIN new_user WITH PASSWORD = 'pass', DEFAULT_DATABASE = old_user_db;

-- new_user now connects to old_user_db by default
-- Confusing for new employee who doesn't realize this

-- Best practice when cloning:
CREATE LOGIN new_user
  WITH PASSWORD = 'TempPassword123!' MUST_CHANGE,
  DEFAULT_DATABASE = master;  -- Explicit, neutral default

-- Then grant permissions on specific databases
GRANT CONNECT TO new_user;

Pitfall 4: DEFAULT_DATABASE = master for Production Developers

-- Anti-pattern: Dev connects to master "to check system stuff"
-- Then accidentally runs a query against master instead of production

-- Better: Set DEFAULT_DATABASE to the actual production database
ALTER LOGIN prod_dev WITH DEFAULT_DATABASE = ProductionDB;

-- Now, if developer forgets to specify USE ProductionDB,
-- queries still run against the intended database

Best Practices

For Regular User Logins

-- Set DEFAULT_DATABASE to the primary database they'll use
CREATE LOGIN sales_analyst
  WITH PASSWORD = 'InitialPassword123!' MUST_CHANGE,
  DEFAULT_DATABASE = SalesDB;

-- Advantages:
-- • Analyst connects and is immediately in SalesDB context
-- • No need to type USE SalesDB every session
-- • Reduces configuration in connection strings

For Service/Batch Accounts

-- Set DEFAULT_DATABASE = master (neutral, always exists)
-- Do NOT use MUST_CHANGE (service can't change password interactively)
CREATE LOGIN etl_service
  WITH PASSWORD = 'ComplexServicePassword456!',
  DEFAULT_DATABASE = master;

-- ETL service then connects and explicitly runs:
-- USE StagingDB;
-- [run ETL]

-- If needed to rotate password:
-- Rotate in a controlled manner (not via MUST_CHANGE)
-- Update connection string or password vault
-- Restart service

For Temporary/Contractor Accounts

-- Always use MUST_CHANGE
-- Set DEFAULT_DATABASE to a limited, safe database
CREATE LOGIN contractor_march2024
  WITH PASSWORD = 'TempOnly123!' MUST_CHANGE,
  DEFAULT_DATABASE = master;

-- Set an expiration date (when contract ends)
ALTER LOGIN contractor_march2024 WITH CHECK_EXPIRATION = ON;

-- Disable after contract ends (instead of deleting)
ALTER LOGIN contractor_march2024 DISABLE;

Monitoring Login Configuration

Find All Logins and Their Defaults

SELECT
  sp.name AS login_name,
  sp.type_desc,
  sp.default_database_name,
  sp.create_date,
  sp.modify_date
FROM sys.server_principals sp
LEFT JOIN sys.databases d
  ON sp.default_database_name = d.name
WHERE sp.type = 'S'  -- SQL logins only
  AND sp.name NOT LIKE '##%'  -- Exclude system logins
ORDER BY sp.name;

Find Logins with Broken Default Database

SELECT
  sp.name AS login_name,
  sp.default_database_name,
  d.name AS database_exists
FROM sys.server_principals sp
LEFT JOIN sys.databases d
  ON sp.default_database_name = d.name
WHERE sp.type = 'S'
  AND sp.default_database_name NOT IN ('', 'master')
  AND d.name IS NULL  -- Database doesn't exist
ORDER BY sp.name;

-- Fix: Alter these logins to use master
-- ALTER LOGIN login_name WITH DEFAULT_DATABASE = master;

Password Change Flow with MUST_CHANGE

SSMS (GUI)

1. User logs into SSMS
2. Dialog appears: "Your password must be changed before using SQL Server"
3. Fields: Current Password, New Password, Confirm New Password
4. User enters all three and clicks OK
5. Password is changed, connection established
6. User is now in their DEFAULT_DATABASE context

sqlcmd (Command Line)

-- Initial login attempt
sqlcmd -S localhost -U john_user -P TempPassword123!

-- Server responds:
-- 1> Your password must be changed before you can access the database.
-- 1> Enter new password:
-- [User types new password]
-- 1> Confirm new password:
-- [User types new password again]
-- 1> Password changed successfully.
-- 1>

The Bottom Line

DEFAULT_DATABASE is often overlooked but critical—set it to a stable, permanent database (usually master). Avoid pointing to production DBs that might be dropped or moved.

MUST_CHANGE is a security feature for new/temporary logins. Use it on contractor accounts and new employees. Never use it on service accounts (they can't interactively change passwords).

Together, these two settings form a robust login onboarding and lifecycle management system. Ignore them, and you'll face mysterious connection failures at 3 AM.

← Back to Blog