Tom Van Harpen (4/4/2013)
We have a team member that does not have sysadmin but we would like to give this person the ability to setup and maintain SQL Agent jobs.We've found that only a member of sysadmin can do this, unless of course this person owns the job. The jobs are all SSIS and all owned by the same SQL Account.
We tried to have them work with the jobs by logging in as this SQL account but then access to the file system is restricted. We've tried all the MSDB roles but nothing seems to works.
I'm just wondering if anyone knows what security settings would give someone the ability to modify Agent jobs? or if there is a work around?
Seeing as all agent jobs are maintained in the msdb database, it makes sense to create a user with access to the database. This link http://msdn.microsoft.com/en-us/library/ms187901.aspx explains how.