Post reply

problem adding a job due to lack of permissions

  • January 17, 2005 at 5:53 pm

    #163514

    I am having problem adding a job as a user John in a database DB1. John has db_datawriter, db_owner roles granted on DB1. When the following part of the code runs,

      -- Add the Target Servers

      EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = ''

      IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    it gives error:

    Msg 15003, Level 16, State 1, Server UDESHMANE, Procedure sp_add_jobserver, Line 43

    Only members of the sysadmin role can execute this stored procedure.

    What role/permissions does the user John need?

    and on which databases?

    Thanks!

     

  • January 18, 2005 at 7:57 am

    #537842

    all job related codes must have a sa level login to execute.

    but, i suggest you should not give sa's login away for security.

  • January 18, 2005 at 8:22 am

    #537849

    Alternatively, you can mess with job ownership. If a job is owned by a specific SQL login (NT groups are not eligible), that login can start, stop, review, etc. that job. Usually jobs are owned by sa (or other SysAdmin), as you then don't have to worry about access rights--particularly if you're running command exec or active script steps, in which case you have to worry about the proxy account.

    It gets ugly quick. I was just reviewing all this in BOL; if you're doing serious job work, you want to dedicate an hour or so to researching this subject.

    A few additional pointers: all SQL Agent jobs (and related code) are stored in the msdb database--nothing job-related is stored in your user databases (least of all access rights!) If not using Enterprise Manager, you can use the various msdb..sp_%job% procedures to manage them via scripts, but this is somewhat less than simple.

       Philip

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply