How to grant permissions to run a specific sql agent job

Question

Post reply

How to grant permissions to run a specific sql agent job

sqlfriend

SSC Guru

Points: 52781
More actions
February 22, 2012 at 10:53 am

#257255

Is there a way to only give permisson to a certain user to run A sql agent job on our production server without abling to see or run other sql agent jobs?
Thank,

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Yuri55 SSCrazy Eights Points: 8449 More actions · Answer 1

As per BOL:"Members of SQLAgentUserRole have permissions on only local jobs and job schedules that they own."

Therefore: 1) add User to SqlAgentUserRole 2) make User the owner of job

sqlfriend SSC Guru Points: 52781 More actions · Answer 2

But we have 3 peoples may take turn to run the job.

And I don't want give them rights to view or run other jobs because this is a production server

Lowell SSC Guru Points: 323518 More actions · Answer 3

sqlfriends (2/22/2012)
But we have 3 peoples may take turn to run the job.
And I don't want give them rights to view or run other jobs because this is a production server

can't you simply create a procedure which executes the job, and give the end users EXECUTE permission on the procedure itself, but nothing related to jobs?

I've done exactly that for creating databases...end users don't have permissions, but the proc creates it on their behalf.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 4

just my 2ct in addition to Lowells reply which serves exactly your question.

Here's another way to avoid having to grant higher privileges ...

Have a look at help to tighten use of cmdshell or sp_start_job[/url]

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

sqlfriend SSC Guru Points: 52781 More actions · Answer 5

The job is running a SSIS package.

Is there a way excute ssis in a stored procedure?

Thanks

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 6

Everything is possible....

If you want to be able to have execution control, use a sqlagent job.