I have a job in SQL Server 2008 that I want to start by invoking it with sp_start_job. I have no problems starting the job when logged in as a user who has the sysadmin role, but I can't start it from a user that doesn't. This makes sense from what I have already read where sysadmins have access to everything. The error I get is
From what I have read I can raise an alert that will then trigger the job. I can also create a staging table to indicate that the job should run and then modify the job to continuously poll this table. Personally, I just want to run the job directly, but I am not sure that roles need to be opened up to the user to make this happen. Any ideas? Obviously, I don't want to make this user a sysadmin. I am thinking one of the SQLAgent roles or via proxy, but I am not sure if this could open up a security threat.
I wouldn't recommend you to add a user to SQLAgentOperatorRole just to let them run a specific job.
Instead I would create a Stored Procedure, which uses WITH EXECUTE AS 'loginname' to execute the job, using a 'loginname' that has permissions to run the job, and then grant EXECUTE for that stored procedure to the user.
Replace 'loginname' with a login that has permission to run the job.
answered Aug 29 '11 at 08:14 AM