I have noticed rather strange behaviour of EXECUTE AS and REVERT sequence during the cross database calls which appear to be a bug. I tested this issue on developer edition of SQL Server 2012
Microsoft SQL Server 2012 - 11.0.5343.0 (X64)
May 4 2015 19:11:32
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
This issue causes problems in SSISDB where similar piece of code appears in [catalog].[start_execution] and some other scripts in the [internal] schema. This was previous discussed in
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/11952f29-f4db-4adc-b813-efd9bed09df3/ssisdb-createexecution-error-the-current-security-context-cannot-be-reverted-please-switch-to-the?forum=sqlintegrationservices
The following script illustrates the issue:
USE [master] GO CREATE DATABASE [Test1] GO CREATE DATABASE [Test2] GO -- Set Database to Trustworthy to allow cross database connection ALTER DATABASE [Test1] SET TRUSTWORTHY ON; GO ALTER DATABASE [Test2] SET TRUSTWORTHY ON; GO USE [Test2] GO CREATE PROCEDURE [dbo].[TestContext] AS SELECT 'EXECUTION CONTEXT BEFORE EXECUTE AS CALLER', SUSER_NAME(), USER_NAME(); EXECUTE AS CALLER SELECT 'EXECUTION CONTEXT AFTER EXECUTE AS CALLER', SUSER_NAME(), USER_NAME(); -- THROWS EXCEPTION -- Msg 15199, Level 16, State 1, Procedure TestContext, Line 38 -- The current security context cannot be reverted. Please switch to the original database where 'Execute As' was called and try it again. REVERT GO USE [Test1] GO -- Execute procedure from database Test1 EXECUTE AS CALLER EXEC [Test2].[dbo].[TestContext] REVERT GO DROP DATABASE [Test2] GO DROP DATABASE [Test1] GO