SQL Server 2000 Job permissions

Posted on 2006-05-10
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

    >  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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    The steps for moving the system databases to a new location are documented in the following technical article: However sometimes after the moving process is finished, though SQL i…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now