When attempting to view the jobs' properties in SSMS, the users would get the error execute was denied on the object sp_help_targetserver. This is a sys SP in MSDB.

The users have

  • SQLAgentReaderRole

  • SQLAgentUserRole

which should be sufficient to view the jobs themselves. These roles are assigned to their AD group. All the users are Win domain users that belong to the AD groups that have those permissions.

Does anyone know why management studio tries to execute sp_help_targetserver just to view jobs? Executing sp_help_job works.

SQL Server Agent Jobs can be targeted at non-local servers, via the "Target Server" property.

enter image description here

In order to make the "Target multiple servers" option enable, you need to setup a Master Server which enables automated administration across an enterprise.

SSMS is running the msdb.dbo.sp_help_targetserver stored procedure to obtain details about the servers that may potentially be listed in the "Targets" property window.

In order to run the sp_help_targetserver stored procedure, the user must be a member of the sysadmin role. If the user is not a member of sysadmin, they can simply ignore the error message. They should, however, be aware that in a Master Server environment the properties window may not show accurate details.

Running msdb.dbo.sp_help_job will, by default, run sp_help_jobserver. However, if you run EXEC dbo.sp_help_job @job_name = 'some_job', @job_aspect = 'JOB';, the target servers are not checked, and not displayed. This may provide a good way for non-sysadmin users to look at job properties, although not via the GUI.

FYI, the Microsoft Docs for SQL Server Agent Roles says this about SQLAgentReaderRole:

SQLAgentReaderRole includes all the SQLAgentUserRole permissions as well as permissions to view the list of available multiserver jobs, their properties, and their history. Members of this role can also view the list of all available jobs and job schedules and their properties, not just those jobs and job schedules that they own. SQLAgentReaderRole members cannot change job ownership to gain access to jobs that they do not already own. Only the Jobs node in SQL Server Management Studio Object Explorer is visible to members of the SQLAgentReaderRole.

The above seems at odds with the Docs for sp_help_targetserver, which clearly states the user must be a member of the sysadmin role.

  • What seems to be really odd here, is for a test user the tab TARGET SERVERS tab when creating a new job has all the options greyed out (nor can they execute the SP). This user can also view job properties. However this user cannot execute the SP_HELP_TARGERSERVER SP. When I try to create a new job and check the TARGET SERVERS tab(when I am logged in with the other users), I get the execute was denied on the object sp_help_targetserver error. I do not understand why it tries to execute this SP... I will try to grant VIEW on this SP and see what happens. – Vaskemaskina Apr 20 at 14:27

I have resolved the issue by basically resetting the permissions. Removed all users under SQLAgentReader and SQLAgentUser roles, and re-added them. also granted execute on SP_HELP_TARGET_SERVERS for the users that had problems. This is not a solution, rather just a workaround.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.