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

by

Troubleshooting Guide: SQL Server Error 9707

Why This Error Happens

Error 9707 occurs when a Service Broker queue references an activation user (via the ACTIVATION clause’s EXECUTE AS setting) whose corresponding database principal has been dropped, renamed, or orphaned from the login mapping. This typically happens after a user account is deleted from the database without first updating or disabling the queue’s activation procedure settings, or when a database is restored/migrated to another server where the Security Identifier (SID) no longer matches an existing login. Since the Queue Monitor cannot resolve a valid security context to execute the stored procedure, it throws this error and disables activation for that queue.

Step-by-Step Resolution Guide

Step 1: Identify the Broken Queue and User ID

  • Query the system views to translate the numeric IDs from the error message into readable names.
  • Run the following in SQL Server Management Studio (SSMS):
SELECT name, principal_id FROM sys.database_principals WHERE principal_id = <User ID from error>;
SELECT name, activation_procedure, execute_as_principal_id FROM sys.service_queues WHERE object_id = <Queue ID from error>;
  • If the first query returns no rows, the principal has been orphaned or dropped, confirming the root cause.

Step 2: Re-map or Recreate the Orphaned User

  • If the user exists in sys.server_principals (the login) but not in sys.database_principals, repair the mapping using the modern syntax (avoid the deprecated sp_change_users_login in SQL Server 2025).
  • Execute the following to fix the orphaned SID:
ALTER USER [YourUserName] WITH LOGIN = [YourServerLoginName];
  • If the user was intentionally deleted, you must create a new user or use an existing valid one to proceed to the next step.

Step 3: Update the Queue’s Activation Context

  • Alter the Service Broker queue to point to a valid, existing database user for the EXECUTE AS clause.
  • Use the ALTER QUEUE command to reset the security context:
ALTER QUEUE [YourQueueName]
WITH ACTIVATION (
    STATUS = ON,
    PROCEDURE_NAME = [YourActivationProcedure],
    MAX_QUEUE_READERS = 5,
    EXECUTE AS N'ValidUserName'
);
  • Ensure the ValidUserName has CONNECT permissions and ownership rights on the target schema.

Step 4: Restart the Queue Monitor

  • Toggle the queue status to force the SQL Server Service Broker engine to re-read the updated metadata and clear the cached error state.
  • Run:
ALTER QUEUE [YourQueueName] WITH STATUS = OFF;
ALTER QUEUE [YourQueueName] WITH STATUS = ON;

Alternative or Verification Steps

  • Verify the Fix: Check the SQL Server Error Log or sys.dm_broker_queue_monitors to confirm the state column shows the queue as RECEIVES_OCCURRING or NOTIFIED rather than an error state.
  • Alternative Fix: If permissions errors persist (Error 9674) after fixing 9707, grant the EXECUTE AS user the specific EXECUTE permission on the stored procedure manually via GRANT EXECUTE ON [ProcedureName] TO [ValidUserName];.