October 31, 2004 at 11:48 pm
I'm planning to delegate some of my admin work to my colleague like the maintenance of the backup. I've already made his user a member of db_backupoperator role within the database he will be maintaining, and made him also the role as the db_owner of the msdb database. My problem was when I test his account, he cannot see current jobs under the Jobs entry in the Enterprise Manager unless he is the one who created it or the owner of the jobs defined. What other privilege do I have to gave to him in order to view all the jobs even though he is not the owner of the job?
November 1, 2004 at 9:33 am
There is role in MSDB called TargetServersRole that allows members to view jobs. It's undocumented and subject to change in future releases and service packs, it works now.
Greg
Greg
November 1, 2004 at 8:32 pm
Thanks for the answer, i'll try it...
November 4, 2004 at 4:09 pm
lebi,
hmmmm i recently came across a similar problem. however mine was slightly different in that i was trying to hand out general day to day checks to the TSD (technical support desk) who i assume have more limited technical skills than your colleague and would just report back to us if any problems are found. for that fact i was very wary what permissions to give them and came across the problem with job ownership that you have found.
unfortunately i believe a user needs to be a member of the sysadmin role to view jobs they don't own. for more detailed info regarding permissions have a look under the permissions section for the entry 'sp_help_job' under the location 'Transact-SQL Reference' in BOL.
i'm not sure if this will help you as you seem to be after delegating more control but my approach was as follows:
i configured the 'Management>Web Assistant Wizard' to run a custom stored procedure that uses a combination of results from sp_help_job and sp_help_jobhistory. the 'Web Assistant Wizard' creates a job which can be scheduled to run under the context of the SQL Server startup account (therefore has necessary permissions), this generates a simple HTML page showing the status of all jobs, which gets dumped via a UNC path to a web server, this job is run every few hours and can be viewed by the TSD without needing any necessary permissions and doesn't rely on email or constantly checking EM or running custom scripts.
ll
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply