none
Error running job from Job Agent - Non Sysadmin have been denied...

    Question

  • Hey Guys,

    I am receiving the following error running my dtsx package from sql 2005 SQL Agent Jobs.

    I get the following error :

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

    I am trying to understand what user he is talking about.

    Is this the Job Owner account I specifiy when i create the job ? Or I use SQL Authentication to my DB in my package is this the user its complaining about ?

    Thanks 

    • Changed type David DyeModerator Tuesday, January 27, 2009 4:20 PM Thread poses a question changed from General Discussion
    Tuesday, January 27, 2009 4:16 PM

Answers

  • 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

    Thx, Jason H


    Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance
    Thursday, January 29, 2009 5:58 AM

All replies

  • The service account that SQL Server agent is running under does not have appropriate permssions to execute an SSIS package.  The below link has outlines solutions to this problem:

    http://support.microsoft.com/kb/918760

     

    Hope this helps


    David Dye
    Tuesday, January 27, 2009 4:32 PM
    Moderator
  • What do you mean when you say Service Account, could you explain what this account is ? Is that the Job Owner Account ?
    Tuesday, January 27, 2009 4:40 PM
  • The SQL Server agent has its own security account that it uses.  The security context of the SQL Server agent account can be found a couple ways:

    1. Go to Start>Run>type in services.msc

    2. Locate the SQL Server agent for the instance of SQL that is running the SSIS package, should be SLQ Server Agent.

    3. Double click the service and check the account assigned to the service

    Or you can use SQL Server Configuration Manager to locate the service account.

     

     


    David Dye
    Tuesday, January 27, 2009 4:53 PM
    Moderator
  • 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

    Thx, Jason H


    Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance
    Thursday, January 29, 2009 5:58 AM
  • thx alot it helped me
    Wednesday, February 22, 2012 8:23 AM