SQL Server Performance

Specified @Job_Name ('testjob') does not exist

Discussion in 'General DBA Questions' started by DBADave, Sep 26, 2005.

  1. DBADave New Member

    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
  2. satya Moderator

    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.
  3. DBADave New Member

    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
  4. satya Moderator

    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.

Share This Page