Agent Security

Overview

Every job on a SQL Server runs as somebody. Which somebody is decided by three things that are set at different times by different people and are never shown together:

  1. The job’s owner.
  2. The proxy on each step.
  3. The Agent service account, which a step with no proxy falls back to.

Getting that combination wrong is not a security opinion. It is an outage.

The Agent Security page: who owns the jobs and what they run as
The whole page in the Who owns the jobs view. Forty two jobs owned by one principal, and a verdict on every row saying what that job will actually run as.

Where to find it

Expand a server in the tree, expand the msdb database, then MSDB → Agent Security.

Shown only on an instance that can have SQL Server Agent.

The three ways this goes wrong

A job owned by a SID with no login

The job fails the moment Agent tries to run it, with an error that names nothing useful. This happens every time a Windows account is deleted, and the jobs that person owned keep sitting in the list looking perfectly healthy until their next scheduled run.

A job owned by a non-sysadmin whose steps have no proxy

Fails the same way, because those steps cannot use the service account. Only a sysadmin owner gets that fallback.

A job owned by sa and left there

Runs as the service account, which is usually far more privileged than the person who wrote the job. That is not an error, and it is worth knowing.

The two views

View What each row is
Who owns the jobs One row per job, with its owner, the state of that account, and what its steps actually run as.
What the proxies reach One row per proxy and per Agent role, with the credential behind it and the subsystems it is allowed into.

The second view is the other side of the question. Membership of the three Agent roles is what decides who can start these jobs, and the members of those roles are the people who can run whatever the proxies can reach.

Role What it can see and do
SQLAgentUserRole Only jobs this login owns are visible at all.
SQLAgentReaderRole Every job is visible; only its own jobs can be operated.
SQLAgentOperatorRole Every job and every run, and can operate them.

Reading the chart

The bars: jobs owned by each principal, with the state of that principal
One bar, because every job on this instance is owned by sa. The line under the header is the consequence: three of them run a step as the Agent service account.

One bar per principal that owns a job, with the job count on the right and the state of that principal under the name.

An owner is either a working login, a login that cannot start these jobs, or a SID with nothing behind it. Red here means broken rather than a category, which is the same rule the grid follows.

Reading the grid

The grid: owner, what it runs as, the state of that account and a verdict
Runs as and State of that account are the two columns that decide whether a job starts at all. The verdicts at the top are the three jobs whose steps fall back to the Agent service account.
Column What it is
Name The job, proxy, credential or role member.
What it is Which of those it is.
Runs as The account the work is actually done under: the proxy’s credential, or the service account for a sysadmin-owned step with no proxy.
State of that account Whether the login exists, is enabled, is a sysadmin, or is a SID with nothing behind it.
Detail The subsystems a proxy is enabled for, or the principal type behind an owner.
Verdict One sentence saying whether this row will run, and what it will run as.

Red on this page always means broken, never a category. An owner SID with no login, a non-sysadmin owner with no proxy, and a proxy pointing at a credential that has been dropped are all red.

The toolbar

Control What it does
Who owns the jobs / What the proxies reach The two views.
Agent activity Opens Agent Activity.
Failed jobs Opens the Failed Jobs report.

Where the data comes from

Source What it gives
dbo.sysjobs The job and its owner_sid.
dbo.sysjobsteps Each step’s proxy_id and subsystem.
dbo.sysproxies, dbo.sysproxylogin, dbo.sysproxysubsystem The proxies, who may use them and what they reach.
sys.credentials The Windows identity behind each proxy.
sys.server_principals Whether the owner SID resolves to a login, and whether it is enabled.
sys.database_role_members, sys.database_principals in msdb Membership of the three Agent roles.

Nothing here is changed. The fixes are one statement each (sp_update_job @owner_login_name, sp_grant_proxy_to_subsystem, ALTER LOGIN) and they belong in somebody’s hands rather than behind a menu item on a report.

Messages you may see

This instance automates nothing through SQL Server Agent. There are no jobs, no proxies and nobody in the Agent roles. On an instance that does run jobs, this page is empty only for a login that cannot see them.

Report Why you would go there
Agent Activity Whether Agent is up, and what is running under these accounts now.
Job Commands What the steps these accounts run actually contain.
Failed Jobs The jobs that are failing, which is where an orphaned owner shows itself.
Job Step Failures The step level message, which is where “unable to determine if the owner has server access” appears.
Third Party Objects Users and roles in msdb that setup did not create.

Frequently asked questions

Every job is owned by sa. Is that a problem? It is not an error, and the page says so rather than flagging it. It does mean every step with no proxy runs as the Agent service account, so the service account’s privileges are the ceiling on what any job on this instance can do.

Why does a job owned by a domain account that still exists show as broken? Because the owner is stored as a SID and the login was dropped and recreated. The account exists in the directory; the login that matched that SID does not.

The page shows fewer jobs than SSMS does. Then this login is in SQLAgentUserRole, which is shown only its own jobs. The caveat line under the summary says so on every msdb page.