There’s been a lot of discussions recently about SQL Agent jobs, proxy accounts, and job ownership. I wanted to try and clarify some of the myths out there, including job ownership and permissions.
The owner of a job is the context of the account, that the SQL Agent Job runs as. This account by default will be the user that creates this job. Normal application level jobs can have this be changed to an application level account, or another non-privileged login which has permissions inside SQ: Server to do the work that it needs to do. ]
The account that SQL Server Agents runs as must have sysadmin rights within the SQL Server Instance otherwise the SQL Server Agent will not be able to run. Also maintenance jobs that rebuild indexes or update statistics are going to need to have at least Database Owner rights within the databases, if not sysadmin rights within the instance.
Jobs for things like Replication and CDC should generally be left alone with sysadmin rights as that’s what they are going to need to function.
Jobs that are created by your application should run with whatever permissions that they need to function.
If you want (or need) to run job steps, where each job step executes as it’s own account, then proxy agents are going to be the way to you. You configure Proxy Accounts per job step in SQL Agent, so you can create different proxies for each job step if you want/have to. These proxy accounts can have whatever permissions the job needs to complete the task at hand. If a job step needs sysadmin rights to complete then you can create a SQL Server proxy for that step, if a job step needs minimal rights for the job step to run, then you can safely grant the proxy just the permissions that it needs.
If you want to give more into the security of SQL Server than I’d highly recommned that you look over a copy of my book, Securing SQL Server and check out my precon at the PASS Summit in 2018.
Denny
The post Who should own your SQL Agent jobs? appeared first on SQL Server with Mr. Denny.
