|
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.
(comments are locked)
|
|
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. Like this: Replace 'loginname' with a login that has permission to run the job.
(comments are locked)
|


I think I might have answered my own question. When I added the SQLAgentOperatorRole to the user in question I was able to start the job with that user. Now I just need to make sure that this is a secure choice.
The operator role will let them run all jobs, you only want them to run one, so go for less permissions as @Magnus has mentioned
Actually, we are looking to give the user access to more than one job. Instead of going down the route of creating a stored procedure for every job, which is doable, are there any concerns of give the user the SQLAgentUserRole role and then give them ownership of the jobs?
If you want the user to be able to alter own jobs, create new jobs etc then SQLAgentUserRole would fit.
The stored procedure I suggested can easily be altered to take the Job Name as a parameter, have execute granted for everyone, and have it check against a table that you create, which jobs the current user has permission to run. Like this.
I would prefer doing that rather than giving the user permissions to the actual job, but that's because I wouldn't ever want to have "my" users creating jobs on their own.
A job can only have one owner, but with "my" solution, more than one user can be allowed execute permissions.
Of course, I should have realized that you could just pass in the job_name. This is exactly what I was looking for, and I like restricting the user by using the WITH EXECUTE AS instead of giving them access to the role.