• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 575
  • Last Modified:

sql server agent permissions

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.
4 Solutions
Vijaya Reddy Pinnapa ReddyCommented:
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.
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.
cheryl9063Author Commented:
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
Anthony PerkinsCommented:
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.
        Yup, its fine. So, just check what a/c is running the job and do as suggested in ID: 38322418

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now