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


