October 27, 2020 at 6:04 pm
Recently, our corporate office forced us to downgrade some non-prod servers from enterprise edition to dev (don't ask, long story). We did so, but now our QA team is having some problems running jobs on the servers. More specifically, a job set up by a vendor to backup & restore a database from server A to server B, being run on Server C - an application server, is failing to even start. It works fine when I run it, but the QA team, who are attached to all SQL Agent roles in msdb, get the following response:
Start failed for Job 'Testers - ThisIsTheRefresh'. (Microsoft.SqlServer.Smo)
------------------------------
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=15.0.18142.0+((SSMS_Rel).190722-0816)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Start+Job&LinkId=20476
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Smo.Agent.Job.Start(String jobStepName)
at Microsoft.SqlServer.Management.SqlManagerUI.StartAgentJobs.StartJobAction.DoAction(ProgressItemCollection actions, Int32 index)
at Microsoft.SqlServer.Management.SqlStudio.Controls.ProgressItemCollection.DoWorkOnThread()
===================================
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType, Boolean retry)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries, Boolean retry)
at Microsoft.SqlServer.Management.Smo.Agent.Job.StartImpl(String jobStepName)
at Microsoft.SqlServer.Management.Smo.Agent.Job.Start(String jobStepName)
===================================
The EXECUTE permission was denied on the object 'xp_sqlagent_notify', database 'mssqlsystemresource', schema 'sys'.
Job 'Testers - ThisIsTheRefresh' started successfully. (.Net SqlClient Data Provider)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.7493&EvtSrc=MSSQLServer&EvtID=229&LinkId=20476
-----------------------------
Server Name: MyAppServerName
Error Number: 229
Severity: 14
State: 5
Procedure: xp_sqlagent_notify
Line Number: 1
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
Every article I find via Google is referencing SSRS, but this is SSMS and the job activity monitor. Plus, this is a database backup & restore, nothing doing with a report. So I'm a little confused as to what could be the cause. Obviously I can't grant specific access to this "phantom" database that's real enough to be referenced but untouchable by us mere mortals.
Any thoughts as to what's going on here?
October 28, 2020 at 6:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
October 29, 2020 at 1:12 pm
The service account does not have access to the folder where the systemresource database is location maybe?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 30, 2020 at 12:59 am
I figured it out finally. Had to grant execute permissions to that proc in master. The error was misleading.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply