How to fix Error 4064 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 4064 — “Cannot Open User Default Database. Login Failed.”

Why This Error Happens

This error occurs when a SQL Server login’s default database is either offline, dropped, corrupted, or the login no longer has valid access permissions to it. Windows Server 2025 and Windows 11 environments are particularly sensitive to this issue after in-place OS upgrades, where SQL Server service accounts may lose mapped permissions during the migration. Additionally, this can happen if the database was detached, renamed, or restored under a different name without updating the login’s default database property.

Step-by-Step Resolution Guide

Step 1: Identify the Login’s Default Database

  • Open SQL Server Management Studio (SSMS) using the SQLCMD mode or connect via sqlcmd -S servername -E -d master since the default database connection will fail otherwise.
  • Run the following query to check which database is causing the conflict:
  SELECT name, default_database_name FROM sys.server_principals WHERE name = 'YourLoginName';
  • Confirm whether the referenced database exists and is online by checking sys.databases.

Step 2: Reset the Default Database to Master

  • Execute the following T-SQL command to temporarily redirect the login to the master database, restoring login capability:
  ALTER LOGIN [YourLoginName] WITH DEFAULT_DATABASE = [master];
  • This bypasses the immediate login failure and allows administrative access to diagnose the original target database.
  • Verify the change took effect by re-running the query from Step 1.

Step 3: Repair or Restore the Target Database

  • Navigate to the SQL Server Configuration Manager and check if the database files (.mdf/.ldf) are present in the expected directory, typically located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
  • If the database shows as “Suspect” or “Offline” in SSMS, attempt to bring it online using:
  ALTER DATABASE [YourDatabaseName] SET ONLINE;
  • If corruption is suspected, run DBCC CHECKDB('YourDatabaseName') to assess the extent of the damage before considering a restore from backup.

Step 4: Validate Windows Server 2025 Permissions

  • Open Services.msc and ensure the SQL Server (MSSQLSERVER) service account has not been reset to a default profile during a Windows Update or Server Core transition.
  • Confirm the service account has “Log on as a service” rights via secpol.msc under Local Policies > User Rights Assignment.
  • Restart the SQL Server service to apply any permission changes.

Alternative or Verification Steps

  • Verification: Attempt to log in via SSMS using the specific login credentials; if successful, run SELECT DB_NAME(); to confirm the session is landing in the correct default database.
  • Alternative Fix: If the original database cannot be recovered, create a new database with the same schema requirements and reassign it as the default using ALTER LOGIN [YourLoginName] WITH DEFAULT_DATABASE = [NewDatabaseName]; to restore user access immediately while data recovery continues in the background.