Re: Help with sp_start_job and xp_sqlagent_proxy_account

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

From: Sue Hoegemeier <Sue_H@nomail.please>
Date: Thu, 02 Jan 2003 06:43:23 -0700

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: cannot acees two databases as owner
    ... it does not matter you are a member of the ... the sysadmin fixed server role or if there is no any other Login which is ... group if it's a domain) and you can login to your SQL Server with, ...
    (microsoft.public.sqlserver.setup)
  • Re: cannot acees two databases as owner
    ... You are going to be able to login to your SQL Server as a sysadmin as long as you have Builtin\Administrators Login exists Otherwise, it does not matter you are a member of the Administrators group on your Windows or not... ... You need to be a member of the sysadmin fixed server role on SQL Server to be able to manage it as you wish. ... Well, if you do not know any other Login's password which is a member of the sysadmin fixed server role or if there is no any other Login which is a member of the sysadmin and you can login to your SQL Server with, then it means you locked yourself out of your SQL Server box. ...
    (microsoft.public.sqlserver.setup)
  • 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)
  • Help with sp_start_job and xp_sqlagent_proxy_account
    ... When the user is not a member of the sysadmin fixed server role, ... The actual SQL Server service on the target server is being run with the ...
    (microsoft.public.sqlserver.security)