SQL Agent Jobs

  • There is a requirement to use application AD account to execute backend SQL Agent Jobs(SSIS Packages) from Batch Framework. Does this account needs Sysadmin access to run the SQL Jobs? Trying to grant the minimum access to run the Jobs, Instead I was thinking to grant the roles i.e. SQLAgentOperatorRole, SQLAgentReaderRole and SQLAgentUserRole. Does this cause any issues?

  • Just needs to be the owner of the job with the right SQLAgent role and then, they can start their own jobs and not interfere with anything else.

    Never give anything sysadmin

  • No, no, no, no....

    Setup a proxy account and assign that proxy account to the Integration Services subsystem in SQL Server Agent.  Then - use that proxy account to run the agent job step that is executing the SSIS package.

    Only setup the necessary permissions for that proxy account in SQL Server - only on the databases and tables needed to allow the SSIS package to process.  Setup permissions at the OS level - which should only be to a shared folder or specific folder references.  Generally this will be a NAS share where files will be located for processing.

    At no time should you change the ownership of the agent jobs to this proxy account - it isn't needed.

     

     

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Sorry yeah I totally missed the SSIS, non proxy account cannot start SSIS jobs unless it’s a sysadmin.  So yeah what Jeffery said.  Use a proxy.

     

    https://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply