Re: How can a non-owner start a job?

#1

Andrew,

There is no straight-forward way to do this. (You could change the code of
sp_start_job, but it is hard to recommend doing that.)

You might be able to cobble together a workaround, something like this:

1. Create a job that runs as sysadmin which wakes up fairly frequently
(every 5 minutes?) and checks for some sort of flag. If the flag (a row in
a table / a file on disk / etc) is present, this job issues the sp_start_job
for your DTS task and removes the flag.

2. Create a stored procedure that allows your trusted person to set the
flag.

Russell Fields
"Andrew Drake" <andrewdrake (AT) hotmail (DOT) com> wrote

Quote:

Hi!

We've encountered the following problem:

There is a DTS package scheduled for execution every night as a SQL Server
Agent Job. Both DTS package and Job are owned by a user who is a member of
the sysadmin role. And it works fine.

However, from time to time it is required to run the DTS package (or -
what
is equivalent - the job) immediately , and it should be done by a user who
is not the owner of the job.

So it raises the question: How can a non-owner start a job?

MSDN states for sp_start_job:
"Execute permissions default to the public role in the msdb database. A
user
who can execute this procedure and is a member of the sysadmin fixed role
can start any job. A user who is not a member of the sysadmin role can use
sp_start_job to start only the jobs he/she owns."

The user who is intended to start the job is not and will be not a
sysadmin.

Is there any workaround to this problem?

Many TIA,
Andrew

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: How can a non-owner start a job?

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: How can a non-owner start a job? - 07-17-2003 , 08:04 AM