SQL Server 2000 Job permissions

Posted on 2006-05-10
Medium Priority
Last Modified: 2008-01-09
Hello everyone.

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?

Best wishes
Question by:acarrier
1 Comment
LVL 75

Accepted Solution

Aneesh Retnakaran earned 1000 total points
ID: 16650030
>  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?
only member of sysadmin can run the jobs

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question