Calling sp_start_job via Linked Server

If you’re trying to execute sp_start_job via a linked server and keep getting the error:

Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 67
The specified @job_name (‘InsertJobNameHere’) does not exist.

,then here are the steps you need to follow to resolve the issue:

  1. First, take stock of your life and ask yourself why you’re doing this in the first place; use an SSIS package man!
  2. Find the login that is being used for the linked server.
  3. Really, really question whether this is the best option for accomplishing the task you’re trying to solve.
  4. On the target server, add the login from step 2 to the msdb database on the target server.
  5. Add the login to the SQLAgentOperatorRole & TargetServersRole on the target server.
  6. Grant EXEC to sp_start_job stored procedure in MSDB to the TargetServersRole on the target server.
USE [msdb]
GO
CREATE USER [linkedServerLogin] FOR LOGIN [linkedServerLogin]
GO
USE [msdb]
GO
EXEC sp_addrolemember N'TargetServersRole', N'linkedServerLogin'
GO
USE [msdb]
GO
EXEC sp_addrolemember N'SQLAgentOperatorRole', N'linkedServerLogin'
GO
GRANT EXEC ON sp_start_job TO [linkedServerLogin]

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: