March 28, 2014 at 12:40 pm
All,
I have a SQL job that reschedules month end jobs across 3 servers, 1 local, 2 remote. We've recently decommissioned one of the remote servers, moving items to a new remote server. So now I'm updating this job with a new linked server. Only, for some reason, the link doesn't seem to read the msdb tables properly.
The linked server is using SQL Server Native Client 10.0, Product SQLServer and Data Source Servername,1433. I do this so I can have multiple linked servers pointing to different databases on the same server without giving away too many permissions for a specific login. It is set up for RPC and RPC Out, and the security is only "Be made using this security context." The design is exactly the same as the linked server that pointed to the old 2005 remote server.
On the new server, the user login has all the proper (and same) permissions as the old server. It includes the SQLAgentUserRole permissions, db_reader, and db_writer.
Here's the code I'm using, snipped from the rest of the job:
DECLARE @TempDate CHAR(8) = (SELECT REPLACE(CONVERT(CHAR(10),DATEADD(d,1,EndDate),101),'/','') FROM MainDB.dbo.AccountingPeriods
WHERE BeginDate < GETDATE()
AND CONVERT(SMALLDATETIME,CONVERT(CHAR(10),EndDate,101),101) >= CONVERT(SMALLDATETIME,CONVERT(CHAR(10),GETDATE(),101),101));
DECLARE @TempDate2 CHAR(8), @NextMonthEnd2 INT;
SET @TempDate2 = (SELECT REPLACE(CONVERT(CHAR(10),DATEADD(d,1,EndDate),101),'/','') FROM [Prod].MainDB.dbo.AccountingPeriods
WHERE BeginDate < GETDATE()
AND CONVERT(SMALLDATETIME,CONVERT(CHAR(10),EndDate,101),101) >= CONVERT(SMALLDATETIME,CONVERT(CHAR(10),GETDATE(),101),101));
SET @NextMonthEnd2 = (SELECT CONVERT(INT,(SUBSTRING(@TempDate,5,4) + SUBSTRING(@TempDate2 ,1,2) + SUBSTRING(@TempDate,3,2))));
DECLARE @SQLCmdJob2 VARCHAR(2000);
SET @SQLCmdJob2 = 'EXEC MyLinkedServer.MSDB.dbo.sp_update_schedule @name = ''Month End'', @enabled = 1, @active_start_date = '
+ CONVERT(CHAR(8),@NextMonthEnd2) + ', @active_end_date = ' + CONVERT(CHAR(8),@NextMonthEnd2) + '; ';
SELECT @SQLCmdJob2
EXEC (@SQLCmdJob2);
When I remove "MyLinkedServer." and run the code locally against the new remote server, it works fine. When I add it back in and run it from the local server, I get the following error message:
Msg 14262, Level 16, State 1, Procedure sp_verify_schedule_identifiers, Line 138
The specified @schedule_name ('Month End') does not exist.
The annoying thing is I can run the below code from the local server and get results just fine. But for some reason, the above code can't find the schedule by name or ID. All I changed was the linked server name. (and then tested with @schedule_id when @name wasn't working).
SELECT * FROM MyLinkedServer.msdb.dbo.sysjobs WHERE name LIKE 'MyMonthEndJob%'
SELECT * FROM MyLinkedServer.msdb.dbo.sysschedules WHERE name = 'Month End'
This is very annoying and headache inducing. Can anyone think of anything I might have missed?
December 9, 2014 at 2:57 pm
I know this is delayed, but I just tangentially heard about this problem.
I bet it's permissions.
From MSDN:
http://msdn.microsoft.com/en-us/library/ms187354.aspx
"Only members of sysadmin can modify a schedule owned by another user."
December 10, 2014 at 4:30 am
William Assaf (12/9/2014)
I know this is delayed, but I just tangentially heard about this problem.I bet it's permissions.
From MSDN:
http://msdn.microsoft.com/en-us/library/ms187354.aspx
"Only members of sysadmin can modify a schedule owned by another user."
Hmmm. I'm not sure I want to give a linked server sysadmin access, but I should just for a moment to test it out.
Thank you for responding. ...[EDIT] But still, why did it work on a previous server but it's not working with the new server?
And I may have gotten a work around for this, but I have to check and see. Obviously it fell off my list a while ago.
December 10, 2014 at 8:54 am
Yeah, linked servers require elevated permissions, it's one of their drawbacks. For data of significant size, ddl_admin is pretty much required on the remote side or your plans could suffer: http://technet.microsoft.com/en-us/library/ms175537(v=sql.105).aspx It's unavoidable perhaps.
However, I wonder if in this particular case you could instead change the owner of the jobs you're trying to remotely administer to a service account that is also used to make the linked server connection. Then maybe you can get around the "owned by another user" problem.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply