Is it possible to set up a security role that will allow
me to give permissions to schedule and run DTS jobs via
the SQL Agent without giving full sysadmin rights to the
job owner? It looks like SQL Agent requires the DTS job
owner to be in the sysadmin role or it will fail to run
the job with the message -- "Non-SysAdmins have been
denied permission to run CmdExec job steps. The step
failed." I don't really want to give my developers full
sysadmin roles to allow their DTS jobs to be scheduled,
but they cannot see the job history or job if I give
ownership to a sysadmin account that will allow it to
run. Is there some other way to address this problem, or
is it just a basic security setting due to the ability to
run pretty much any command via DTS? Seems like there
should be a way to run these sorts of jobs without making
the developer a sysadmin. Thanks for any info. Please
remove the "no-spam" from the return e-mail address.

From Books Online topic "xp_sqlagent_proxy_account"-

**************

SQL Server Agent proxy accounts allow SQL Server users who do not belong to
the sysadmin fixed server role to execute xp_cmdshell and own SQL Server
Agent jobs. The administrators can assign appropriate security permissions
to the proxy account to control the ability of these jobs to access
resources in the network.

When a SQL Server user executes a command prompt command using xp_cmdshell,
the command must execute in the security context of a Windows account. If
the SQL Server user is a member of the sysadmin fixed server role, SQL
Server executes the command prompt command using the Windows account under
which the SQL Server service is running. If the SQL Server user executing
xp_cmdshell is not a member of the sysadmin fixed server role, SQL Server
executes the command using the Windows account specified as the SQL Server
Agent proxy account. If no SQL Server Agent proxy account has been set, the
user gets an error. SQL Server Agent jobs also must execute in the security
context of a Windows account. If the job is owned by a member of the
sysadmin fixed server role, the job executes using the Windows account under
which the SQL Server service is running. If the job owner is not in
sysadmin, the job executes using the SQL Server Agent proxy account, and an
error is raised if no proxy account has been set.

xp_sqlagent_proxy_account sets or retrieves the proxy account for the
instance on which it is executed. The SQL Server service for that instance
must be running under a Windows administrator account to read or set the SQL
Server Agent proxy account.

**************

So you could setup the proxy account to allow non-sysadmin users to execute
the job, but will the job work? Since the job will execute under a lower
privileged account this my initself cause problems. Alternatively giving the
proxy account increased privaliges would be a security risk.

Perhaps a simple workaround is to use an in-direct mechanism. For example,
users insert a row into a table that indicates they wish a job to be
executed. A regular running job polls this table looking for work. If found
it starts the requested job. This was the user is not drectly calling the
job.


--
Darren Green
http://www.sqldts.com





"David Knapp" <david.w.knapp (AT) no-spam (DOT) questdiagnostics.com> wrote