Re: Help with sp_start_job and xp_sqlagent_proxy_account

From: Sue Hoegemeier (Sue_H@nomail.please)
Date: 01/03/03 

From: Sue Hoegemeier <Sue_H@nomail.please>
Date: Thu, 02 Jan 2003 19:12:51 -0700

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

On Thu, 2 Jan 2003 10:41:33 -0400, "Ed"
<edmundrobinson@hotmail.com> wrote:

>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" <Sue_H@nomail.please> wrote in message
>news:6cg81vs5jqsaji4amu9mp4897mdqpm9qp2@4ax.com...
>> 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
>>
>> On Thu, 2 Jan 2003 09:10:10 -0400, "Ed"
>> <edmundrobinson@hotmail.com> wrote:
>>
>> >
>> >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
>> >
>>
>

Relevant Pages