May 22, 2014 at 12:59 pm
I have a SQL Agent job on Server1 with 3 steps. The 3rd step is supposed to execute a SQL Agent job on Server2.
exec [Server2].msdb.dbo.sp_start_job N'Populate EMISResidentialAddress' --Executes the 'Populate EMISResidentialAddress' scheduled job on the other server SQL server
go
This step runs as the default DOMAIN\SQLHQReport
The linked server connects to Server2 as a user called TEST2
I've gone as far as giving both of those users sysadmin and explicit GRANT EXECUTE on sp_start_job and still get the following error:
Executed as user: DOMAIN\SQLHQReport. The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229). The step failed
What am I missing?
May 22, 2014 at 6:18 pm
Can you post the script to create the linked server?
Also, who is the owner of both SQL Agent jobs?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 22, 2014 at 8:32 pm
SQLRNNR (5/22/2014)
Can you post the script to create the linked server?Also, who is the owner of both SQL Agent jobs?
/****** Object: LinkedServer [SomeServer.SomeDomain.NET] Script Date: 05/22/2014 22:28:54 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'SomeServer.SomeDomain.NET', @srvproduct=N'SQL Server'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SomeServer.SomeDomain.NET',@useself=N'False',@locallogin=NULL,@rmtuser=N'TEST2',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'SomeServer.SomeDomain.NET', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SomeServer.SomeDomain.NET', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SomeServer.SomeDomain.NET', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SomeServer.SomeDomain.NET', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SomeServer.SomeDomain.NET', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SomeServer.SomeDomain.NET', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SomeServer.SomeDomain.NET', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SomeServer.SomeDomain.NET', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SomeServer.SomeDomain.NET', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'SomeServer.SomeDomain.NET', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SomeServer.SomeDomain.NET', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SomeServer.SomeDomain.NET', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SomeServer.SomeDomain.NET', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
Both jobs are owned by 'sa'
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply