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 09:10:10 -0400

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: 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: 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: 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)
  • 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: 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)