Error: @job_name ('sync_juris_matter_update_with_rwdb') does not exist

I am trying to update certain fields in the table of one database (database A) when changes are made to a different database's table (database B).  
Presently I am trying to create a trigger on the table in database B that will start a SQL Agent job with my desired T-SQL language to do the required changes in database A.  But Im getting the error "@job_name ('sync_juris_matter_update_with_rwdb') does not exist".  Ive double checked the job name spelling, even copied and pasted the job name into the trigger.
Here is the trigger Im running:

CREATE TRIGGER [Sync Matter updates with RWdb] ON [dbo].[Matter_Log]
FOR INSERT, UPDATE, DELETE
AS
EXEC msdb.dbo.sp_start_job @job_name = 'Sync_Juris_matter_updates_with_RWdb'

Ive also tried copying the 'sp_start_job' and 'sp_verify_job_identifiers' SPs into the database A's SP repository and then modifying the trigger's EXEC statement to:

EXEC sp_start_job @job_name = 'Sync_Juris_matter_updates_with_RWdb'

but still get the same error.  Can anyone lend a hand on what Im doing wrong?

FYI, I tried creating a trigger in the table of database B to directly update the table in database A.  However, database B is the database of a 3rd party vendor's application and has it's own username account with an unknown password.  This username account is tied to the dbo server login.  Therefore I got a permission error when the trigger would run because database B uses dbo with the sa username account and there is apparently no way to specify alternate credentials to use in T-SQL.
WilbatAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott PletcherConnect With a Mentor Senior DBACommented:
Then you should be fine ... assuming that user has the authority to do whatever the job needs to do.
0
 
Ken SelviaRetiredCommented:
If you connect as the user doing the update on Matter_Log and execute

EXEC msdb.dbo.sp_start_job @job_name = 'Sync_Juris_matter_updates_with_RWdb'

does it work interactively in query analyzer?
0
 
WilbatAuthor Commented:
I cannot connect as the user doing the update on Matter_Log.  All updates would be done via the 3rd part app I mentioned and the app makes all changes via a username who's password is unknown.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Scott PletcherSenior DBACommented:
The user doing the UPDATE does not have authority to the job: since it can't "see" the job, the job does not exist (as far as that user is concerned).

From BOL under "sp_start_job":
Permissions
A user who is not a member of the sysadmin role can use sp_start_job to start only the jobs he/she owns.


This can be tricky to resolve, since SQL will likely use the proxy account.  And you likely have not set up a proxy account and even if you did you would not likely have given it 'sa' authority, which you don't really want to do anyway.
0
 
WilbatAuthor Commented:
OK, what if I simply assign that user as the job's Owner?
0
 
WilbatAuthor Commented:
Setting the user from database B as the job's Owner seems to have fixed the problem.
Thx Scott
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.