Bug in REVERT during cross database call

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

July 27th, 2015 11:32pm

I am not sure what the bug would be. The error message is pretty clear. If you do EXECUTE AS and then change the database, you are not permitted to run REVERT. So the behaviour seems to quite expected.

Free Windows Admin Tool Kit Click here and download it now
July 28th, 2015 3:36am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics