none
SSISDB create_execution error: The current security context cannot be reverted. Please switch to the original database where 'Execute As' was called and try it again.

    Question

  • I'm trying to execute SSIS package from service broker activation procedure.

    When SP is located in my database, I get following error:

    "The current security context cannot be reverted. Please switch to the original database where 'Execute As' was called and try it again."

    When the same SP is located in SSISDB, it works fine, but for consistency reasons I would prefer to have this procedure in my database.

    To emulate this error we we can use following SP:

    ALTER PROCEDURE [dbo].[ExecutePackage]
    WITH EXECUTE AS 'dbo'
    AS
    BEGIN
    
    DECLARE
    	 @execution_id BIGINT;
    
    EXEC [SSISDB].[catalog].[create_execution] 
    	@package_name=N'Loader_DynamicPool.dtsx',
    	@execution_id=@execution_id OUTPUT,
    	@folder_name=N'SSIS Concurent',
    	@project_name=N'SSIS Concurent',
    	@use32bitruntime=False,
    	@reference_id=Null;
    
    DECLARE @var0 SMALLINT = 1;
    
    EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
    	@execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0;
    
    exec [SSISDB].[catalog].[set_execution_parameter_value]
    	@execution_id,  @object_type=50, @parameter_name=N'SYNCHRONIZED', @parameter_value=1;
    
    EXEC [SSISDB].[catalog].[start_execution] @execution_id;
    
    END;

    and then call:

    EXEC [dbo].[ExecutePackage]

    Works in SSISDB, fails in user database.


    Sunday, May 18, 2014 9:30 PM

Answers

  • Error is not about accessing the database, but reverting security context, so I suspect some problem with the call stack.

    I ran a trace of SP:StmtStarting events with your proc and see the SSISB catalog procs execute a REVERT in the SSISDB database context.  That explains the error. 

    As a workaround, you could run you proc via a SQL Agent stat-up job instead of with SB queue activation, with an infinite loop.  That would avoid the issues with the security context revert.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, May 25, 2014 1:07 AM

All replies

  • Hi Piotr,

    According to the error message, it seems that the database role dbo cannot access the SSISDB database under your database context. Could you modify the procedure to change the Execute As account from dbo to a Windows account that has sufficient permissions on both the SSISDB database and your own database?

    Regards,
    Mike Yin

    If you have any feedback on our support, please click here


    Mike Yin
    TechNet Community Support

    Tuesday, May 20, 2014 4:27 PM
  • "dbo" is already mapped to Windows account and this account is a sysadmin.

    It is also mapped in both databases.

    Error is not about accessing the database, but reverting security context, so I suspect some problem with the call stack.

    Saturday, May 24, 2014 8:27 PM
  • Error is not about accessing the database, but reverting security context, so I suspect some problem with the call stack.

    I ran a trace of SP:StmtStarting events with your proc and see the SSISB catalog procs execute a REVERT in the SSISDB database context.  That explains the error. 

    As a workaround, you could run you proc via a SQL Agent stat-up job instead of with SB queue activation, with an infinite loop.  That would avoid the issues with the security context revert.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, May 25, 2014 1:07 AM
  • Thanks for error explanation.

    I was trying to avoid SQL Agent jobs - with activation stored procedure I can ran multiple threads in parallel and easily manage maximum number of concurrent packages.

    With agent jobs I should have multiple jobs or start new jobs on demand with some custom code to manage threads count.

    So I wonder if there is any simple workaround, which would allow to execute these commands in synchronous mode within context of SSISDB database with minimal overhead.

    Sunday, May 25, 2014 1:51 AM
  • Have you found a resolution to this that does NOT involve SQL Agent?

    I'm in the same boat as you, seeing the same errors and logs about "cannot revert current security context".

    Thanks in advance


    Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.

    Monday, June 30, 2014 9:23 PM