locked
SQL jobs suddenly can't be run manually "Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account" RRS feed

Answers

  • Hi vnat,

    Based on the error message, the issue occurs because the current job owner is not the sysadmin on the SQL Server 2008 R2 server. In this condition, the job is forced to run under a SQL Server Agent Proxy account. However, the current job is configured to run under the SQL Server Agent Service Account not the SQL Server Agent Proxy account, and that causes the error.

    To resolve the issue, you need to either assign the job owner to the sysadmin role or configure the job to run under a proxy account. When using a SQL Server Agent Proxy account, make sure the job owner has permissions to use the proxy account. To do this, we can use the following statements:

    USE msdb ;
    GO
    EXEC dbo.sp_grant_login_to_proxy
        @login_name = N'test',
        @proxy_name = N'Proxy1' ;
    GO

    Or you can do it via GUI:

    1. Right click the proxy account in SSMS and select Properties.
    2. In the Principals tap add the non-sysadmin job owner.

    Reference:
    http://blogs.msdn.com/b/dataaccesstechnologies/archive/2009/10/13/who-owns-my-job-and-who-runs-it.aspx

    Regards,
    Mike Yin

    If you have any feedback on our support, please click here


    Mike Yin
    TechNet Community Support


    • Edited by Mike Yin Thursday, November 14, 2013 9:52 AM
    • Marked as answer by Mike Yin Monday, November 18, 2013 2:25 PM
    Thursday, November 14, 2013 9:51 AM

All replies

  • I think the account you connect with has not been assign the canned db_ssisoperator role (at the very minimum, to run a package).

    Arthur My Blog

    Monday, November 11, 2013 6:21 PM
  • Hi vnat,

    Based on the error message, the issue occurs because the current job owner is not the sysadmin on the SQL Server 2008 R2 server. In this condition, the job is forced to run under a SQL Server Agent Proxy account. However, the current job is configured to run under the SQL Server Agent Service Account not the SQL Server Agent Proxy account, and that causes the error.

    To resolve the issue, you need to either assign the job owner to the sysadmin role or configure the job to run under a proxy account. When using a SQL Server Agent Proxy account, make sure the job owner has permissions to use the proxy account. To do this, we can use the following statements:

    USE msdb ;
    GO
    EXEC dbo.sp_grant_login_to_proxy
        @login_name = N'test',
        @proxy_name = N'Proxy1' ;
    GO

    Or you can do it via GUI:

    1. Right click the proxy account in SSMS and select Properties.
    2. In the Principals tap add the non-sysadmin job owner.

    Reference:
    http://blogs.msdn.com/b/dataaccesstechnologies/archive/2009/10/13/who-owns-my-job-and-who-runs-it.aspx

    Regards,
    Mike Yin

    If you have any feedback on our support, please click here


    Mike Yin
    TechNet Community Support


    • Edited by Mike Yin Thursday, November 14, 2013 9:52 AM
    • Marked as answer by Mike Yin Monday, November 18, 2013 2:25 PM
    Thursday, November 14, 2013 9:51 AM