Executing SSIS Package from a stored procedure with different user privileges

I'm having issues with allowing my users to execute SSIS Packages in a reasonable manner due to varying levels of privilege required.

The scenario: we've created a data warehouse, with two different SSIS packages responsible for loading it with data, one is to be run automatically (via a SQL Agent job, and is working fine), and another that must be run on-demand by users once upstream data is finalised and cleansed etc.

This package performs very privileged operations including backing up the database at the start of the run (to be sure, to be sure), dropping and recreating calculated tables etc.

I've written a stored procedure to execute this job via the [SSISDB].[catalog].[create_execution] and [SSISDB].[catalog].[start_execution] stored procedures.... this works fine when run under my account (I'm a sysadmin).

The stored procedure failed when run by a normal user due the higher level of permissions required in SSISDB and MSDB to enqueue the execution, and the package itself failed because it is running under their (lowly) security context.

What I've tried:

I attempted to resolve the issue using 'Execute As' in the stored procedure, however this failed due to the cross-database chaining issues, Trustworthy flag etc.

I've also attempted to solve the issue by having Agent jobs to run the package, and just running the agent job from the stored procedure, however I rapidly entered a world of pain involving:

• The inability to set execution permissions on a per-job basis

• The hope to configure this access via a central Server Role to cater for changing staff over time, and jobs can only have a single user as owner

• The dark world of Proxy accounts, Credentials in combination with sql-auth logins etc

Plans C and D

The only options I can think of remaining to me are to create a dedicated SQL Server Login with elevated permissions, and trust the users not to pass the credentials around/lose auditability of who scheduled the import (how this problem is solved

For posterity I got this working via the following:

• Altering the stored procedure called by the users (Admin.RunImport) to 'Execute as' the account used by the SQL Service

• The SQL Server service account (an AD Managed Service account) is modified to have permissions to execute the Admin sproc allowing the use of execute as above

• The SQL Server service account is modified to have ssisdb.ssis_admin and msdb.SQlAgentOperator roles.

• This Admin.RunImport stored procedure queues up one of 3 Agent Jobs using sp_start_job dependent on a passed parameter

• This redirection via SQL Agent is required to bypass the ssis security context error above

• The agent jobs are owned by 'sa', and simply execute an underlying stored procedure (Raw.hp_Execute_Import_Impl) passing in a different parameter per job.

• This means that the Agent job runs as sa due to the ssis_admin privelege above, the same as the scheduled jobs

• The Raw.hp_Execute_Import_Impl stored procedure queues the SSIS Package as sa the same as normal.

In lieu of being able to create dedicated windows accounts for this purpose I think this as good as I'm going to get at the moment.

Thanks for the help guys!

StackExchange Database Administrators Q#125962, answer score: 2