June 1, 2002 at 1:13 am
Hello,
I have a job configured on one server, named Server A, to insert a row into a table in the master database on another server, named Server B. A trigger exists on the table that will execute the two sp_start_job commands below by passing the job name.
EXEC ServerB.msdb.dbo.sp_start_job @job_name = 'First Job'
EXEC ServerB.msdb.dbo.sp_start_job @job_name = 'Second Job'
The job on Server A is returning the below error:
The specified @job_name ('First Job') does not exist. [SQLSTATE 42000] (Error 14262) The specified @job_name ('Second Job') does not exist. [SQLSTATE 42000] (Error 14262)
These jobs do not exist on Server A, but do exist on Server B. Does anyone know how to resolve this? The servers are linked and the login used for the linked server has INSERT permission on the table with the trigger and SELECT permission to the msdb.dbo.sysjobs table. The login also has permission to run sp_start_job.
Thanks so much!
Melanie
June 1, 2002 at 11:16 am
How is your linked server defined for server B from A as far as login process?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 1, 2002 at 9:01 pm
Hello,
The login used by the linked server used to be a system administrator on both servers and the job worked well with these permissions.
Since the password has become known, I have since removed the administrator privileges and now have the issue described. This is a list of the permissions that the linked server's login has on Server B in regards to this job:
1. Is a member of the public role for the master database, which holds the table the job on Server A needs to insert into.
2. S/I/U/D permissions on the table in the master database.
I am going to try to resolve the issue by creating a stored procedure that contains the EXEC sp_start_job commands and have the job on Server A run this stored procedure instead. I am doubtful this will work, but I am willing to try anything.
Thank you very much for your response and assistance,
Melanie
June 1, 2002 at 10:08 pm
What login owns the job? From BOL for sp_start_job:
Execute permissions default to the public role in the msdb database. A user who can execute this procedure and is a member of the sysadmin fixed role can start any job. A user who is not a member of the sysadmin role can use sp_start_job to start only the jobs he/she owns.
Andy
June 2, 2002 at 8:15 am
Hello,
Thank you for your response.
Last night, I created a stored procedure on Server A that contained the INSERT statement and that would be called by the job on Server A. The step still failed with the same error, that the jobs didn't exist.
I read your reply this morning and the jobs on Server A and Server B were owned to sa. I have since designated the linked server's login as the owner of all three jobs. I started the job on Server A again but that still failed.
I am thinking I would need to create on Server A the two jobs that are on Server B.
Thank you for your help!!
Melanie
June 2, 2002 at 8:31 am
Can you change you settings back the way they were before the failure and then change them one at the time and test. Also since Agent runs the jobs how is it's login defined to run?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 2, 2002 at 8:43 am
Hello,
Are you recommending changing the linked server's login permissions? Or the owner of the jobs?
Thanks!
Melanie
June 2, 2002 at 8:54 am
I think you need to go one step a time. If I understand what you're doing, something like this:
1) Create the job and test it until it works.
2) Add the trigger to the table (on same server as job right?), insert a row, see if the job starts correctly. Continue until it works.
3) Set up your linked server and grant the login used for it insert permissions on the table in #2 - insert a row via the linked server, test until it works - which should fire the trigger which starts the job.
Andy
June 2, 2002 at 8:56 am
One other comment - depending on what you're doing, an easier way is to have the job poll to see when to run. You can do this by having the trigger on the table insert a row into yet another table - every x mins the job runs, if there is a row(s) in the table in runs, removes those rows. The trigger on the table that runs the job directly probably makes sense for situations with a very low incidence, otherwise running the job at whatever interval doesnt cost much and is easier to manage.
Andy
June 2, 2002 at 9:50 am
Also you know it worked previously, you need to find what specific change you made before it failed. From your statement
quote:
The login used by the linked server used to be a system administrator on both servers and the job worked well with these permissions.Since the password has become known, I have since removed the administrator privileges and now have the issue described. This is a list of the permissions that the linked server's login has on Server B in regards to this job:
It looks like you have a permission issue for the login to be used. What about that relates to how Agent runs a job?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 3, 2002 at 8:32 am
Hello,
Thank you very much for everyone's help. I ended up creating new jobs, a new trigger, and a new table. To make a long story short, the owner of the jobs on Server B must be the login used in the linked servers. I was able to get the insert and trigger working successfully.
After all of this, which by the way is great research, the job that alters a database requires the login to be a sysadmin! :o) Of course, I don't want this because the linked server is accessible to most all developers.
I'll need to think of another way to accomplish my tasks without giving the linked server's login too much access.
Thanks again!!
Melanie
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply