Search code examples
sqlsql-serverssissql-agent-jobsql-agent

Execute One Sql Agent Job by 2 users

I have a scenario, where there is a dev and prod environment. All SSIS development will happen in dev SQL Server, and later it's given to a separate team to deploy in production.

So now my requirement is, as a developer of the SSIS package, I should be able to connect to the prod server through SSMS and execute the job deployed by the other team in prod. And I should not be able to see any other jobs on the server.

What is the best setup for this scenario?

Solution

  • The SSMS UI can only do what it can do. Beyond the approach outlined by JodyT, where you'll be able to see all jobs but only executed the ones you own, you are asking for something custom, therefore you're going to need a custom solution.

    In this case, I would most likely solve the problem with a stored procedure. We'll leverage the EXECUTE AS clause to let you run the stored procedure but when it runs, the actions will be performed as the privileged user.

    msdb.dbo.sp_start_job is the method that is called when you right click on a job and "Start Job at Step..."

    Knowing that, the name of the job you want to run and the person who can run it is all you need to create the appropriate stored procedure.

    CREATE PROCEDURE 
    dbo.AnandPhadkeRunJob
WITH EXECUTE AS 'domain\username'
BEGIN
    SET NOCOUNT ON;
    EXECUTE msdb.dbo.sp_startjob @job_name = 'Do Something';
END

    Here I create a stored procedure that starts the job called "Do Something" and runs it under the context of the "username" domain account. Tailor that to match your environment.