6

I've just read this article which advocates using the sa account for ownership of sql server agent jobs.

http://sqlmag.com/blog/sql-server-tip-assign-ownership-jobs-sysadmin-account

He has a couple of caveats but doesn't provide much detail on these.

He does say the following:

...in situations where security is a concern, and where least privilege makes sense (such as with jobs other than backups, consistency checks, index defrags, etc.) then you'll want to look at AND take advantage of SQL Server proxies.

My question is - why does least privilege not make sense with backups, consistency checks, index defrags, etc? What is the rule of thumb for when least privilege can be ignored?

8

Maybe the wording is unclear, but least privilege applies to all activities and should never be ignored.

The least privilege you need to perform database maintenance is sysadmin. Lower privileged users could perform those activities too (I'm thinking of db_owners), but the least privilege needed to perform any activity on a SQL Server instance is sysadmin, so if you have a job that performs administration tasks, it makes sense to have it running as a sysadmin.

For tasks other than database maintenance (tasks that implement some business logic), you want to impersonate users that have permissions to perform specific activities on the database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.