SQL Server Agent Jobs – Principle of Least Privilege

best practicespermissionssql serversql-server-agent

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?

Best Answer

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.

Related Solutions

SQL Server – SQL Server Agent Jobs and Availability Groups

Within your SQL Server Agent job, have some conditional logic to test for if the current instance is serving the particular role you are looking for on you availability group:

if (select
        ars.role_desc
    from sys.dm_hadr_availability_replica_states ars
    inner join sys.availability_groups ag
    on ars.group_id = ag.group_id
    where ag.name = 'YourAvailabilityGroupName'
    and ars.is_local = 1) = 'PRIMARY'
begin
    -- this server is the primary replica, do something here
end
else
begin
    -- this server is not the primary replica, (optional) do something here
end

All this does is pull the current role of the local replica, and if it's in the PRIMARY role, you can do whatever it is that your job needs to do if it is the primary replica. The ELSE block is optional, but it's to handle possible logic if your local replica isn't primary.

Of course, change 'YourAvailabilityGroupName' in the above query to your actual availability group name.

Don't confuse availability groups with failover cluster instances. Whether the instance is the primary or secondary replica for a given availability group doesn't affect server-level objects, like SQL Server Agent jobs and so on.

SQL Server – Cleaning Disabled SQL Agent Jobs

Only you can answer if the jobs can be dropped completely. If they are not jobs like from the import/export wizard I would probably script them out. This can be done easily with PowerShell.

A quick way of dropping them is via Object Explorer details pane, it allows multiple selections and then just hit the delete key or right click and delete.

Best Answer

Related Solutions

SQL Server – SQL Server Agent Jobs and Availability Groups

SQL Server – Cleaning Disabled SQL Agent Jobs

Related Question