CODEWITHSUNDEEP
sql-serversql-server-2008-r2
user1945753
user1945753

Reputation: 133

Executing a SQL Server job remotely via another job

In the past this command would work: 

EXEC [linkedServerName].msdb.dbo.sp_start_job @job_name = 'test2'

Now I have to use the impersonate login and add the SQL service account (referenced in local login to impersonate) of calling server onto called server and giving it SA privilege: think I am doing something wrong, it shouldn't be so complicated should it?

Without the SA privilege on the impersonating account added to the remote server I receive error:

The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.

Thank you

Upvotes: 0

Views: 2253

Answers (1)

Bacon Bits
Bacon Bits

Reputation: 32220

it shouldn't be so complicated should it?

This isn't any more complicated than the task you're doing. It's pretty basic security. If you want an account to be able to do things on a server, the account needs to have the proper access to that server. I'm not sure why you think it should be otherwise. Setting up proxy accounts isn't the most straightforward, but scheduling jobs from a remote server instead of the local agent isn't the most straightforward task, either.

Per the doc a user needs to be a member of the sysadmin, SQLAgentUserRole, SQLAgentReaderRole, or SQLAgentOperatorRole to be able to call sp_start_job.

The alternative is to configure the linked server to use a fixed security context or map a security context instead of impersonating one. This can be configured on the Security page of the Linked Server properties. Note that the account used for a fixed context still needs to be a member of one of the above roles to execute sp_start_job.

Upvotes: 1

Related Questions