Re: Help with sp_start_job and xp_sqlagent_proxy_account

From: Ed (edmundrobinson@hotmail.com)
Date: 01/02/03 

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

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

  • Re: When creating a new table, owner is DBO I need another user
    ... database is mapped to the special user inside each database called dbo. ... any object created by any member of the sysadmin fixed server role ...
    (microsoft.public.sqlserver.security)
  • Re: Default User
    ... database is mapped to the special user inside each database called dbo. ... any object created by any member of the sysadmin fixed server role ...
    (microsoft.public.sqlserver.security)
  • Re: Permission clarification - dbo vs domainuser.dbo
    ... database is mapped to the special user inside each database called dbo. ... any object created by any member of the sysadmin fixed server role ...
    (microsoft.public.sqlserver.security)
  • Re: Help with sp_start_job and xp_sqlagent_proxy_account
    ... >>>user is not a member of the sysadmin fixed server role and the job has ... >>>domain administrators account. ...
    (microsoft.public.sqlserver.security)
  • Re: Help with sp_start_job and xp_sqlagent_proxy_account
    ... A user who is not a member of the sysadmin role can use ... >user is not a member of the sysadmin fixed server role and the job has not ... >sp_start_job will impersonate the SQL Server Agent proxy account, ... >user group to the sysadmin server role. ...
    (microsoft.public.sqlserver.security)