Specified @Job_Name ('testjob') does not exist | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Specified @Job_Name (‘testjob’) does not exist

I created a stored procedure that starts a SQL job owned by SA. I granted execute permission on the stored procedure to a SQL Server id with no special permissions. When I try to run execute the stored procedure I receive the error message about the job_name not existing. I thought the stored procedure would start the job since the stored procedure is owned by SA, but apparently ownership chaining does not affect scheduled jobs. I then added the SQL Server login id to TargetServersRole in msdb and granted Execute permission against sp_start_job to TargetServersRole. This appears to have addressed the issue, however I’m not sure if I opened any security holes by doing this. Any thoughts? Thanks, Dave
The TargetServerRole is used for Target servers to be able to see jobs on a Master server. It was a potential security issue to allow members of that role to be able to execute jobs, this is introduced after SP3 release. If that job doesn’t deal in changing any potential data, then you no issues and otherwise you may need to keep a close watch on the process. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Satya, From my tests this morning it appears the ID I added to TargetServersRole and the Grant Execute on sp_start_job To TargetServersRole allows the ID to execute any SQL Server job. I was hoping that wasn’t the case. Here is what I did. Created SQL job called ‘TestJob’ (owned by SA), which executes two stored procedures
Created SP to execute sp_start_job ‘TestJob’
Granted Execute on SP to Test_ID
Granted Test_ID access to msdb
Added Test_ID to TargetServersRole
Granted Execute Permission on sp_start_job to TargetServersRole I’m trying to permit an ID to execute only the one SQL job. One alternative is to make the SQL ID owner of the job and make sure the ID has the necessary permissions to execute the store procedure and access the tables referenced by the stored procedure. I would like to avoid this alternative. Any suggestions? Dave
I believe the role is applicable on all the jobs on sQL Server and there is no such facility to ascertain such single job privileges. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>