job run as part of trigger/non-sysadmin

We are on SQL 2005.
We have several jobs that get started as part of Triggers on inserts. The
job in question has a T-SQL step.
When the user who creates a record is part of the sysadmin role, everything
is fine. When the user is not part of that role, we get
The specified @.job_name ('the job name') does not exist.
How can we use a user that is not in the sysadmin role to run this job?
Thanks,
DougThis cannot be done directly. In SQL 2005 you could have the trigger post a
message to a Service Broker queue, and have a sysadmin level process
assigned to processing queue messages, thus using the queue to decouple
permissions, one of Service Broker's intended purposes.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Doug" <Doug@.discussions.microsoft.com> wrote in message
news:4125E54B-E0E2-464D-9A2E-04A9A8BB501A@.microsoft.com...
> We are on SQL 2005.
> We have several jobs that get started as part of Triggers on inserts. The
> job in question has a T-SQL step.
> When the user who creates a record is part of the sysadmin role,
> everything
> is fine. When the user is not part of that role, we get
> The specified @.job_name ('the job name') does not exist.
> How can we use a user that is not in the sysadmin role to run this job?
> Thanks,
> Doug
>|||Daniel Jameson (djameson@.childrensoncologygroup.org) writes:
> This cannot be done directly. In SQL 2005 you could have the trigger
> post a message to a Service Broker queue, and have a sysadmin level
> process assigned to processing queue messages, thus using the queue to
> decouple permissions, one of Service Broker's intended purposes.
Yes, Service Broker is most certainly the way to do. Most likely the
Agent job would not be need at all then, but what needs to be done can
be performed by the activation procedure for the queue.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx