SQL Agent Security in SQL Server 2000

Question

SQL Agent Security in SQL Server 2000

Ellen-477471

SSCrazy

Points: 2238
More actions
August 30, 2010 at 1:53 pm

#227052

What is the minimum security a user needs to be able to see all jobs -- not just their own, and be able to start any job.
I did not want to give sysadmin to the user just to allow access to the jobs scheduled in SQL Agent.
Thank you,
Ellen

Viewing 5 posts - 1 through 4 (of 4 total)

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

If you are using SP3 or above of SQL 2000, you have to add the user to the TargetServerRole in MSDB so that they can view the status of the jobs owned by sysadmin

-Roy

Ellen-477471 SSCrazy Points: 2238 More actions · Answer 2

Thank you so much. I am going to go read to find out what the 'TargetServerRole' is. I am not familiar with that role.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

Great, Please do let us know if that was helpful to you. There are couple of others who are facing the same issue.

-Roy

Ellen-477471 SSCrazy Points: 2238 More actions · Answer 4

I assigned the user to the TargetServerRole. The user then could see all of the jobs but received an error message that basically said 'cannot execute sp_start_job" . I tried to find a description of the role but have not found it anywhere in online documentation. Does anyone have a link to that info?

Also, online in forums I have found conflicting information. Some people say that the role allows execution of jobs and others say it doesn't.

Is there a document from MS that gives the facts?

Thank you for any assistance.