Creating a job from a trigger

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.slamm wrote:
> 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.
I don't think that's going to work. If it did, then any user who had job
creation rights could alias a job as the system administrator, giving
them more rights in the process. You can grant the user rights to do
what needs to run or you can do the following:
Instead of creating a job directly from the trigger, insert the
necessary job criteria into a custom table of application jobs (to be
created). Create a recurring job on the server that monitors this table
at specified intervals and creates the jobs itself. Then you don't even
have to grant users job creation rights. They only need rights to your
application job table.
David Gugick
Imceda Software
www.imceda.com