• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2766
  • Last Modified:

Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account.

Hi All,
one of user is trying to execute a dts package is getting this error

Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account.  The step failed.

any ideas?
Thanks!
0
msdba
Asked:
msdba
  • 2
3 Solutions
 
jcott28Commented:
Found this info:

The person starting the job is not 'sa' or a member of sysadmin server role, so they will not have enough permission to run the DTS/SSIS job step by default, because that involved launching a program (DTExec.exe).

You could create a credential (windows account & password which is remembered in SQL) and make a Proxy account (pointer to the credential for a certain job subsystem) then use the proxy account to run this job.

The credential you choose should be a windows account with enough permissions to run DTExec.exe and access the database systems needed inside your package design (including any file shares, program files folders, registry keys, etc).

When the non-sa user tried to launch the job, the SQL Agent will impersonate the designated proxy account to run a child process DTExec.exe to run the SSIS package.

Here's a tutorial http://www.jumpstarttv.com/creating-a-proxy-account_147.aspx
0
 
SQLSergentMikeCommented:
Is there a reason you do not have them in one of the predefined roles for SSIS -db_dtsadmin, db_dtsltduser, and db_dtsoperator? It would seem to make sense to just put them in the appropriate role?
0
 
SQLSergentMikeCommented:
If this is 2000 that would make sense, either create a proxy, or make him owner, or sa. There are not alot of options in 2000, best to upgrade if thats the case. They discontinued support for 2000 anyways.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now