Post reply

Access for non sa user to run a DTS package

  • March 29, 2006 at 9:05 am

    #168330

     
    SQL Server 2000 SP3.
     
     
    I am attempting to allow someone that does NOT have 'sa' rights to execut a dts package that they created... what rights can I assign them instead of just giving them 'sa'..

    Non-SysAdmins have been denied permission to run CmdExec job steps. The step failed.

  • March 29, 2006 at 9:33 am

    #629543

    I think if you set a role in msdb for them and grant execute on sp_startjob, they can do it.

    Alternatively, create a job that queries a table (some new one) for a value and then runs the job. Let the user insert rows into the table when they need the job run. This way you can setup time restrictions, not run more than once, etc.

  • March 29, 2006 at 11:34 am

    #629608

    They'll also need to own the job or be a member of the TargetServersRole role, though this latter option is not supported by MS. There's code in a view, sysjobs_view, which sp_start_job relies on that forces this. The view sysjobs_view is called by sp_verify_job_identifiers, which itself is called by sp_start_job.

    K. Brian Kelley
    @kbriankelley

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

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