Help with sp_start_job and xp_sqlagent_proxy_account

[Ed]

Hello

Does anyone know how to have a user start a job using sp_start_job when that
user is not a member of the sysadmin fixed server role and the job has not
been created by them??

I have a calculation intensive stored procedure (my_sp_x) that takes over an
hour to run, saves the results in a table and notifies the user via SQL Mail
that it has completed. I set up a separate stored procedure
(my_sp_parms_start_ x) to take the parameters for my_sp_x, save them to a
table and then start a job named "Run X",

EXECUTE msdb.dbo.sp_start_job @job_name = 'Run X'

that has one step that executes my_sp_x.

This all works great from the SQL Query Analyzer were I am connected as
database user sa. However when I try and start the job from the application
where I am connected as myself via a user group with permissions on the
application database I get and error,

The specified @job_name ('Run X') does not exist.

So I read up on sp_start_job some more and it says,

When the user is not a member of the sysadmin fixed server role,
sp_start_job will impersonate the SQL Server Agent proxy account, which is
specified using xp_sqlagent_proxy_account.

However I can not find this stored procedure in the help files. Also the
network guys are really strict about users roles and I cannot change the
user group to the sysadmin server role.

The actual SQL Server service on the target server is being run with the
domain administrators account. I tried changing the job's owner to the
domain administrator since the user group was not in the owners list but I
get the same error.

Does anyone know how to have a user start a job using sp_start_job when that
user is not a member of the sysadmin fixed server role and the job has not
been created by them??

Many thanks

Edmund

[Sue Hoegemeier]

A user who is not a member of the sysadmin role can use
sp_start_job to start only the jobs he/she owns.

-Sue

[Ed]

Sue

I know this. That is why I am asking this question. Do you know what
xp_sqlagent_proxy_account does and how I can use it in this case?

Edmund

"Sue Hoegemeier" <Su...@nomail.please> wrote in message
news:6cg81vs5jqsaji4am...@4ax.com...

[Sue Hoegemeier]

Depending on the values you pass to it (SET or GET),
xp_sqlagent_proxy_account sets or retrieves the proxy
account. I can't think of a way you could use it in this
case as the proxy doesn't impact who can start what jobs but
rather it affects the security context of jobs running under
non-sysadmin accounts.

-Sue