monitor jobs in sql server

[daproud]

I have a user that would like to monitor jobs in sql server. How can I set this user up to monitor jobs using EM without giving him the sa role? I tried to set permissions on the sysjobs, sysjobhistory, sysjobserver and sysjobschedules tables in msdb but he still couldn't see the jobs.
Thanks,
Dave

 

[mrdenny]

To see all the jobs in SQL Server you have to be sa, or you have to be the one who created the job.

Assign the job ownership to him, and he should be able to see the job. This also means that he will be able to start and stop the jobs, not just view them. It also means that the job will run under his account, not the sa account.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[JayKusch]

Another option is to create scripts and/or views on the sysjobshistory table and such. Then he can execute stored procedures you create that give him the needed information of the jobs.

Thanks

J. Kusch

 

[mrdenny]

This is always true. A nice web based interface that pulls from the system tables would do the trick. It would let them see what they needed to, but they wouldn't actually be able to do anything with it (unless you wanted them to be able to).

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[Corran007]

I had to do this very thing. Basially the major purpose was to allow a non admin to enable and disable reoucring jobs when there was a problem with them. so i created a custom asp.net appliation that connects to it. i am hitting the system tables directly. works fabulous. im working on giving him history next if i can ever find time to work on it again.

 

[mrdenny]

Sounds good. Be sure to test this before installing the next service pack on this server. The last service pack changed the jobs tables a little.

Denny

--Anything is possible. All it takes is a little research. (Me)