SQL Server 2000 Job permissions
Posted on 2006-05-10
What I am trying to do is to create an account that will be able to see, modify, delete and run jobs without the account being system admin. Part of the issue is that I need some jobs to be owned by a sysadmin to run DTS packages, but that can be run by a non system admin. Unless I am forced to do so, I don't want to setup a proxy account for SQLserver agent.
I have looked at this for a while, and it looks like in the MSDB database there is a role called TarverServersRole that permits you to see all the jobs. The problem is that it has been denied a series of permissions, among them to run and modify jobs. I have tried to create a new msdb user role, with the same permissions that we given to TargetServersRole with none of the restrictions, but for some reason in enterprise manager I can't see any jobs. In query analyzer I can't run them using sp_start_job, I get an error message that the job does not exist.
I guess I could modify TargetServersRole, but its a role defined by default with msdb, and who knows what I will break if I modify all those permissions in it.
So my questions are :
- Why doesn't the new role I create with the permissions mimicked from TargetServersRole work? I can't see a difference between the 2.
- What permissions (master and msdb permissions) do I need to set for a user to be able to see/modify/delete/run jobs without being system admin?