Post reply

Creating a Job from a Trigger - Problem

  • February 10, 2005 at 12:56 pm

    #109493

    Hello, All

    I have a trigger on a table, which when called, creates a job to run in n

    minutes. The trigger runs fine and the job is created, however with a small

    issue.

    The username that is used as the Job Owner, ends up to be the name of the

    user that triggered the trigger eventhough the code that creates the job

    specifies a different name.

    What am I doing wrong? How can I resolve this?

    Here is the SP that is run in the trigger:

    exec msdb..sp_add_job @job_name = 'TEST',

     @owner_login_name = 'sa',

     @notify_level_eventlog = 0,

     @delete_level = 1

    The code above says user SA but the job ends up running as TESTUSER and it

    makes the job fail because a that user is not a SA nor able to run a CMD

    job.

    Please help!

    Thank you.

  • February 11, 2005 at 10:35 am

    #541845

    Specify the account 'sa' when you create the job step that runs the CMD job.  From BOL:

    [@database_user_name =] 'user'

    Is the name of the user account to use when executing a TSQL step. user is sysname, with a default of NULL. When user is NULL, the step runs in the job owner's user context on database.

    So long, and thanks for all the fish,

    Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3

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

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