Re: Run Jobs in Enterprise Manager

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Allan Mitchell (allan_at_no-spam.sqldts.com)
Date: 02/20/04 

Date: Fri, 20 Feb 2004 20:32:37 -0000

According to BOL these are the rules for sp_start_job

Permissions
Execute permissions default to the public role in the msdb database. A user
who can execute this procedure and is a member of the sysadmin fixed role
can start any job. A user who is not a member of the sysadmin role can use
sp_start_job to start only the jobs he/she owns.

When sp_start_job is invoked by a user who is a member of the sysadmin fixed
server role, sp_start_job will be executed under the security context in
which the SQL Server service is running. 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. If
the proxy account is not available, sp_start_job will fail. This is only
true for Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there
is no impersonation and sp_start_job is always executed under the security
context of the Windows 9.x user who started SQL Server. 

-- 
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"MF" <anonymous@discussions.microsoft.com> wrote in message
news:A0A3CEA4-2E4F-4F2E-BA20-012EE7C270E1@microsoft.com...
> I have several DTS packages in Enterprise Manager that are run as jobs in
SQL Server Agent. I want to assign the task of running the jobs to someone
who is not a member of the sysadmin role and who is not the owner of the
jobs. How do I do this?

Relevant Pages

  • RE: exec sp_help_job user account rights
    ... Execute permissions default to the public role in the msdb database. ... who can execute this procedure and is a member of the sysadmin fixed role can ... the SQL Server service is running. ... impersonation and sp_help_job is always executed under the security context ...
    (microsoft.public.sqlserver.security)
  • Re: Job in a package
    ... But you will have to sort out the permissions issues, around running jobs. ... Vyas, MVP ... > called from an SQL Execute task of DTS packages. ... > Vyas, MVP (SQL Server) ...
    (microsoft.public.sqlserver.dts)
  • SQL Server Jobs - Never stops execution
    ... I have a typical problem with SQL Server Jobs. ... We use VBScript files to execute scheduled, batch jobs. ...
    (microsoft.public.sqlserver.setup)
  • Re: grant access to extended properties
    ... Do you know anything about this "EXECUTE AS" for 2000? ... I played around with GRANT but apparently, a member of "db_datareader" ... Reading Books Online tells us that to add extended properties, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Scheduled DTS import job never stops
    ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... www.SQLDTS.com - The site for all your DTS needs. ... > I have an SQL Server 2000 aplication with DTS import jobs scheduled each ... When I execute it again it works ...
    (microsoft.public.sqlserver.dts)