sql server agent permissions

Posted on 2012-08-22
Last Modified: 2012-08-30
I have a job that runs on server A.. The job agent is called jobagent_A.. This job updates a table on a database on server B..  In order for the job to run what permissions do I need to give jobagent_A and where? The database on server B is called Database2.
Question by:cheryl9063
    LVL 9

    Assisted Solution

    You'll have to grant the vendor some permissions in msdb. I'd create a role, add the user to it, and grant the following permissions to the role:

    sp_add_job sp_add_jobstep sp_add_jobschedule
    sp_update_job sp_update_jobstep sp_update_jobschedule
    sp_help_job sp_help_jobstep sp_help_jobschedule
    sp_delete_job sp_delete_jobstep sp_delete_jobschedule


    Unless you make the vendor a user in other databases, he won't be able to access anything in them.
    LVL 25

    Assisted Solution

    You need to create a login in server B and for the login you need to create user in the database where the table exists. You also need to specify db_reader, db_writer role on the database for the user.
    LVL 1

    Author Comment

    The SQL job agent in Server A is running the job.. Not a vendor, not a user.. The Job agent in Server A is running a job that updates a table in server B
    LVL 75

    Assisted Solution

    by:Anthony Perkins
    First of all the user is the SQL Server Agent service startup account.  So that account will need permissions to execute the job.
    Without knowing more details on the job, I cannot be more precise.
    LVL 25

    Accepted Solution

            Yup, its fine. So, just check what a/c is running the job and do as suggested in ID: 38322418

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    This video discusses moving either the default database or any database to a new volume.

    737 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

    18 Experts available now in Live!

    Get 1:1 Help Now