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

by

Troubleshooting Guide: SQL Server Error 505 — “Changing Databases Is Not Allowed”

Why This Error Happens

This error occurs because the current session context was altered using SETUSER or SP_SETAPPROLE, both of which lock the session to a specific security context tied to the original database. SQL Server intentionally blocks database-switching (USE <database> or cross-database calls) during this state to prevent privilege escalation or context leakage between databases. This is a security-driven restriction, not a bug — it typically surfaces in scripts, stored procedures, or ORM-generated code that mixes impersonation logic with multi-database queries.

Step-by-Step Resolution Guide

Step 1: Identify Where the Context Switch Is Triggered

  • Review the connection’s session initialization scripts, login triggers, or stored procedures for calls to SETUSER or SP_SETAPPROLE.
  • Check application connection strings and middleware (e.g., Entity Framework, Dapper) for embedded role-switching logic.
  • Run SELECT * FROM sys.dm_exec_sessions WHERE session_id = @@SPID; to inspect the current security context.

Step 2: Revert the Session Context Before Switching Databases

  • Ensure REVERT is explicitly called immediately after the impersonated action completes, before any USE <database> statement executes.
  • If using SP_SETAPPROLE, capture and store the returned cookie value, then call sp_unsetapprole @cookie to safely restore the original context.
  • Example correction pattern:
  EXEC sp_setapprole 'MyAppRole', 'Password123', @fCreateCookie = true, @cookie = @cookie OUTPUT;
  -- perform required actions in current database only
  EXEC sp_unsetapprole @cookie;
  USE AnotherDatabase; -- now allowed

Step 3: Redesign Cross-Database Logic to Avoid Mid-Session Switching

  • Split the stored procedure or script into separate batches/connections — one per database — instead of switching mid-session under an application role.
  • Use three-part naming (DatabaseName.Schema.Table) with a properly scoped login instead of USE statements when impersonation is active.
  • On Windows Server 2025 / Windows 11 hosts, confirm SQL Server 2025 connection pooling isn’t reusing a “poisoned” session that still holds an app role context from a prior request — reset connection pooling via DBCC FREESESSIONCACHE if needed.

Step 4: Audit and Patch Application Code or ORM Configuration

  • Search application source code (C#, Java, PowerShell) for any raw SQL execution blocks containing SETUSER or SP_SETAPPROLE.
  • Update ORM interceptors or connection factories to isolate impersonated sessions from multi-database transaction scopes.
  • Deploy the corrected build/script to a staging environment first to confirm the error no longer triggers under load.

Alternative or Verification Steps

  • Verify the fix by executing the affected stored procedure or application workflow again and confirming no Error 505 appears in SQL Server Error Logs (SQL Server Configuration Manager > SQL Server Logs) or Windows Event Viewer.
  • As an alternative, if SP_SETAPPROLE isn’t strictly required, consider using contained database users or EXECUTE AS USER = 'username' with REVERT, which offers more predictable session-scoping behavior across databases.