0

I need to run a job as SQLAgentReaderRole and it is working fine (as the job contains Proxy, etc.). BUT I need also to modify somehow the role to specific user and specific job.

For example: I have 3 different jobs (Job1,Job2,Job3) and all of can be seen in Jobs drop-down in SSMS. Also I need to run only Job2 without seeing the Job1 and Job3. Is it possible to do it somehow (of course the Filter is not an option)?

Thanks!

Improve this question

1 Answer 1

Reset to default
1

You could create a stored procedure that runs the job. You can use with execute as owner to run the stored procedure as the database owner. That way, the user doesn't need permissons on sp_start_job.

create procedure dbo.DoYourJob
with execute as owner
as
exec sp_start_job @job_name = 'YourJob'

Grant execute rights on DoYourJob to allow specific users to start the job.

this might be useful as well Grant execute permission to an SQL Server user to run a specific job only

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Is there anyway to do it in SSMS GUI, instead of using stored procedure? I have a lots of parameters and it will be better for users to run the job through the GUI.
like this?
"OWNER Specifies the statements inside the module executes in the context of the current owner of the module. If the module does not have a specified owner, the owner of the schema of the module is used" - which is often the owner of the DB but not always.
@MitchWheat do i have to correct anything in my answer?
@CarloPrato is it possible to add more than 1 owner to a JOB?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.