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

by

Troubleshooting Guide: Error 2797 — “The Default Schema Does Not Exist”

Why This Error Happens

This error occurs when a SQL Server login is mapped to a database user whose default schema has been dropped, renamed, or was never properly created in that database. It commonly happens after a schema cleanup, a database migration, or when a user is created with CREATE USER referencing a DEFAULT_SCHEMA that doesn’t exist yet. SQL Server 2025 validates schema existence more strictly during session initialization, causing login or query execution to fail until the mapping is corrected.

Step-by-Step Resolution Guide

Step 1: Identify the Affected User and Schema Mapping

  • Connect to the instance via SQL Server Management Studio (SSMS) or sqlcmd.
  • Run the following query to check the current default schema:
  SELECT name, default_schema_name FROM sys.database_principals WHERE type = 'S';
  • Note which user has a default_schema_name that does not correspond to an existing schema in sys.schemas.

Step 2: Verify Existing Schemas in the Database

  • Run this query to list all valid schemas:
  SELECT name FROM sys.schemas;
  • Confirm whether the expected schema (e.g., dbo) is missing or was renamed/dropped.

Step 3: Recreate the Missing Schema or Reassign the Default Schema

  • If the schema should exist, recreate it:
  CREATE SCHEMA [SchemaName] AUTHORIZATION [dbo];
  • If the schema is no longer needed, reassign the user’s default schema to dbo instead:
  ALTER USER [UserName] WITH DEFAULT_SCHEMA = dbo;
  • Ensure the target schema path exists under Databases > [YourDB] > Security > Schemas in SSMS.

Step 4: Restart the Affected Session or Service

  • Close and reopen the SSMS connection, or restart the SQL Server service via:
  services.msc > SQL Server (MSSQLSERVER) > Restart
  • This clears cached session metadata that may still reference the invalid schema.

Alternative or Verification Steps

  • Re-run the login or query that originally triggered Error 2797 to confirm it now executes without the schema error.
  • If the issue persists, use sp_change_users_login (SQL Server 2019 and earlier compatibility mode) or re-map orphaned users via ALTER USER ... WITH LOGIN = [LoginName] to ensure the login-to-user-to-schema chain is fully intact.