September 17, 2014 at 9:21 am
Situation: Server A needs a job run after a specific job step on Server 1's nightly job completes. We've done this before between Server A (our App server) and Server D (our database server). It's usually pretty easy with linked servers and a bit of t-sql.
Unfortunately, we now have to read a job on a Web server that's in a different domain.
I set up the linked server with a new SQL login specifically for this purpose. I gave the SQL login read permissions on Server 1. I even ran GRANT EXECUTE on the dbo and sys schemas. But I keep running into the following error:
Msg 229, Level 14, State 5, Procedure xp_sqlagent_enum_jobs, Line 1
The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'.
Now I can read MSDB on Server 1 from Server A. (I ran "SELECT TOP 10 * FROM [linkedserver].msdb.dbo.sysjobs;" to be sure). I just can't execute the extended stored procedure, even after granting execute perms on Master and MSDB. I can't seem to find the resource database to grant execute on that. When I do a "USE mssqlsystemresource" statement on Server 1, I get this error:
Msg 911, Level 16, State 1, Line 1
Database 'mssqlsystemresource' does not exist. Make sure that the name is entered correctly.
I've read a couple of articles on uninstalling and reinstalling certificates & msdtc, which I do NOT want to do unless it's the only solution. These are production machines. Server 1 runs a vended software solution and Server A is our in-house 24/7 app server. If I start fiddling without being absolutely sure those changes will fix the problem, my boss will serve my head up on a pink slip.
Does anyone have any thoughts as to other potential solutions?
September 17, 2014 at 9:52 am
What query are your trying to execute to get job status?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 17, 2014 at 9:57 am
Shawn Melton (9/17/2014)
What query are your trying to execute to get job status?
[linkedserver].master.dbo.xp_sqlagent_enum_jobs
September 17, 2014 at 10:10 am
Grant execute explicitly to that procedure for the login being used.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 19, 2014 at 6:41 am
Shawn Melton (9/17/2014)
Grant execute explicitly to that procedure for the login being used.
Odd that granting EXECUTE on the entire schema in Master didn't work, but explicitly granting that proc did.
Thanks, Shawn!
September 19, 2014 at 7:04 am
Brandie Tarvin (9/19/2014)
Shawn Melton (9/17/2014)
Grant execute explicitly to that procedure for the login being used.Odd that granting EXECUTE on the entire schema in Master didn't work, but explicitly granting that proc did.
Thanks, Shawn!
DAMN. That works for just a select, but it won't let me put it into an INSERT statement.
IF (SELECT OBJECT_ID('tempdb..#xp_results')) IS NOT NULL
DROP TABLE #xp_results;
DECLARE @is_sysadmin int, @job_owner sysname;
SET @is_sysadmin = 0;
SET @job_owner = NULL;
CREATE TABLE #xp_results
(job_id UNIQUEIDENTIFIER NOT NULL, last_run_date INT NOT NULL,
last_run_time INT NOT NULL, next_run_date INT NOT NULL,
next_run_time INT NOT NULL, next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, request_source INT NOT NULL,
request_source_id sysname NULL, running INT NOT NULL,
current_step INT NOT NULL, current_retry_attempt INT NOT NULL,
job_state INT NOT NULL, Jobname varchar(1000) ) ;
-- set sysadmin flag
SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0);
-- set job owner
SELECT @job_owner = SUSER_SNAME();
--Note FROM SSC "Grant execute explicitly to that procedure
-- for the login being used"
-- populate #xp_results
INSERT INTO #xp_results (job_id, last_run_date, last_run_time,
next_run_date, next_run_time, next_run_schedule_id, requested_to_run,
request_source, request_source_id, running,
current_step, current_retry_attempt, job_state)
EXECUTE [linkedserver].master.dbo.xp_sqlagent_enum_jobs @is_sysadmin,
@job_owner;
If I comment out the INSERT INTO #xp_results lines, then the code works fine. If I let the INSERT ride, then I get the following error:
OLE DB provider "SQLNCLI10" for linked server "linkedserver" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 23
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "linkedserver" was unable to begin a distributed transaction
Any additional suggestions?
September 19, 2014 at 8:45 am
Brandie Tarvin (9/19/2014)
Shawn Melton (9/17/2014)
Grant execute explicitly to that procedure for the login being used.Odd that granting EXECUTE on the entire schema in Master didn't work, but explicitly granting that proc did.
Thanks, Shawn!
I can't prove it but would guess it is because it is an extended stored procedure.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 19, 2014 at 8:54 am
I'm not sure if you need all the columns returned from that procedure but you can get job activity for an instance by querying msdb.dbo.sysjobactivity.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 19, 2014 at 10:46 am
Shawn Melton (9/19/2014)
Brandie Tarvin (9/19/2014)
Shawn Melton (9/17/2014)
Grant execute explicitly to that procedure for the login being used.Odd that granting EXECUTE on the entire schema in Master didn't work, but explicitly granting that proc did.
Thanks, Shawn!
I can't prove it but would guess it is because it is an extended stored procedure.
Except we can get it to work in other circumstances. This is recycled code, just pointing to a different server.
September 19, 2014 at 4:23 pm
Compare the MSDTC settings between two working systems (one being the one you are trying to run the procedure from) and between the two systems that aren't working. Only suggestion I can come up with here. That always seemed to be the problem at a previous employer when distributed transactions were involved.
September 24, 2014 at 4:31 am
Lynn Pettis (9/19/2014)
Compare the MSDTC settings between two working systems (one being the one you are trying to run the procedure from) and between the two systems that aren't working. Only suggestion I can come up with here. That always seemed to be the problem at a previous employer when distributed transactions were involved.
Thanks, Lynn. I'll check those today.
Assuming these pre-month end fires go out any time soon. @=)
September 24, 2014 at 8:26 am
BAH! All the settings are the same between both servers. Pics attached.
I'm beginning to think I'm fighting a losing battle here and might have to go with just selecting data directly out of the sysjob* tables instead.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply