Sql-server – Executing stored procedures asynchronously using tasks in SSIS

You need to grant the EXECUTE privilege, but there is something else you need to know.

When you create a Stored Procedure (SP), make sure the SQL SECURITY of the SP is DEFINER and not INVOKER. Why?

• When you call a Stored Procedure that has DEFINER for SQL SECURITY, the caller is allowed to have the same grants as the DEFINER for the duration of the call. The GRANT EXECUTE for the specified Stored Procedure is necessary.
• When you call a Stored Procedure that has INVOKER for SQL SECURITY, the caller is expected to have the needed grants. If any of the needed grants are missing, the call will fail at the earliest point where the needed grant was missing.

In plain English, if SP has INVOKER for SQL SECURITY, you have to give the table privileges away to the user permanently before the call is made. When SP has DEFINER for SQL SECURITY, the table privileges are given away temporarily (as long as the SP is running).

I discussed this before

• Sep 11, 2012 : Is it possible to encapsulate a table in MySQL?
• Apr 08, 2013 : Why does this procedure raise a privilege error?

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!