Quick Scan Report – Agent Jobs that Run at Startup

What this check looks for

Agent jobs with a schedule whose frequency type is “start automatically when SQL Server Agent starts”, which is freq_type = 64 in msdb.dbo.sysschedules. The message names the job. The check is skipped on Amazon RDS.

Why it matters

A startup job is code that runs automatically, with Agent’s privileges, at a moment when nobody is watching. That is useful and it is also exactly what an attacker would use.

The legitimate uses are real:

• Warming a cache, by running the queries that populate the buffer pool after a restart.
• Re-establishing state that does not survive a restart, such as starting a Service Broker activation or a continuous monitoring loop.
• Clearing down temporary artifacts or resetting flags after an unplanned restart.
• Notifying somebody that the instance has restarted, which is genuinely useful.

The concern is that it is the natural place to hide something. A startup job:

• Runs without anybody triggering it, so it does not appear in anybody’s routine.
• Survives restarts, which is the definition of persistence.
• Runs as the Agent service account or a proxy, which on many instances is highly privileged.
• Is not where people look. A review of scheduled work usually means looking at the nightly and hourly jobs. A job with no time based schedule is easy to skip past.

So the finding is not “this is wrong”. It is “confirm you know what this is”, and the answer should be available in under a minute for each one. A startup job whose purpose nobody can state is a real finding regardless of what it turns out to do.

There is an operational angle too. A startup job runs during the busiest few minutes of an instance’s life, when the buffer pool is empty, statistics may be recompiling and the application is reconnecting. A heavy startup job competes with recovery and with the first wave of user work, so even a benign one can make a restart take noticeably longer to settle.

How to confirm it yourself

Every job with a startup schedule:

SELECT j.[name]            AS [job_name],
       j.[enabled],
       j.[description],
       SUSER_SNAME(j.[owner_sid]) AS [job_owner],
       s.[name]            AS [schedule_name],
       s.[enabled]         AS [schedule_enabled],
       j.[date_created],
       j.[date_modified]
  FROM msdb.dbo.sysjobs          AS j
 INNER JOIN msdb.dbo.sysjobschedules AS js ON js.[job_id] = j.[job_id]
 INNER JOIN msdb.dbo.sysschedules    AS s  ON s.[schedule_id] = js.[schedule_id]
 WHERE s.[freq_type] = 64
 ORDER BY j.[name];

What they actually do, which is the point of the exercise:

SELECT j.[name]          AS [job_name],
       st.[step_id],
       st.[step_name],
       st.[subsystem],
       st.[database_name],
       st.[proxy_id],
       st.[command]
  FROM msdb.dbo.sysjobsteps      AS st
 INNER JOIN msdb.dbo.sysjobs     AS j  ON j.[job_id] = st.[job_id]
 INNER JOIN msdb.dbo.sysjobschedules AS js ON js.[job_id] = j.[job_id]
 INNER JOIN msdb.dbo.sysschedules    AS s  ON s.[schedule_id] = js.[schedule_id]
 WHERE s.[freq_type] = 64
 ORDER BY j.[name], st.[step_id];

Read subsystem first. TSQL is the ordinary case. CmdExec, PowerShell, SSIS or an ActiveX subsystem on a startup job is where the attention should go, because those execute outside the engine.

Who owns it and what it runs as:

SELECT j.[name]                   AS [job_name],
       SUSER_SNAME(j.[owner_sid]) AS [owner],
       st.[step_name],
       st.[subsystem],
       p.[name]                   AS [proxy_name],
       c.[credential_identity]
  FROM msdb.dbo.sysjobs      AS j
 INNER JOIN msdb.dbo.sysjobsteps AS st ON st.[job_id] = j.[job_id]
  LEFT JOIN msdb.dbo.sysproxies   AS p ON p.[proxy_id] = st.[proxy_id]
  LEFT JOIN sys.credentials       AS c ON c.[credential_id] = p.[credential_id]
 WHERE st.[proxy_id] IS NOT NULL;

A job owned by a sysadmin with no proxy runs as the Agent service account, which is the most privileged option available.

When it was created or last changed, which is the single most useful column for deciding whether this is yours:

SELECT [name], [date_created], [date_modified], [enabled]
  FROM msdb.dbo.sysjobs
 ORDER BY [date_modified] DESC;

A startup job created at a date that does not correspond to any change you made is worth investigating properly.

And whether it actually runs:

SELECT j.[name] AS [job_name],
       msdb.dbo.agent_datetime(h.[run_date], h.[run_time]) AS [started],
       h.[run_duration],
       h.[run_status],
       h.[message]
  FROM msdb.dbo.sysjobhistory AS h
 INNER JOIN msdb.dbo.sysjobs  AS j ON j.[job_id] = h.[job_id]
 WHERE h.[step_id] = 0
 ORDER BY h.[run_date] DESC, h.[run_time] DESC;

The startup entries line up with the instance start times, which is a quick way to correlate.

How to fix it

Account for each one. Then keep, document or remove it.

1. Read the command of every step. If you can state in one sentence what it does and why it has to run at startup, it is fine.
2. If you cannot account for it, treat it as a security finding rather than a cleanup item. Do not simply delete it, because the command text is the evidence. Script the whole job out first:

-- keep a record before changing anything
SELECT j.[name], st.[step_id], st.[step_name], st.[subsystem], st.[command]
  FROM msdb.dbo.sysjobs AS j
 INNER JOIN msdb.dbo.sysjobsteps AS st ON st.[job_id] = j.[job_id]
 WHERE j.[name] = N'TheJobInQuestion';

3. Disable rather than delete while you find out:

EXEC msdb.dbo.sp_update_job @job_name = N'TheJobInQuestion', @enabled = 0;

4. Document the legitimate ones. Put the purpose in the job description, where the next person will find it:

EXEC msdb.dbo.sp_update_job
     @job_name    = N'Warm Buffer Pool',
     @description = N'Runs at Agent startup to pre-load the Orders and Customers '
                  + N'clustered indexes into the buffer pool. Owner: DBA team.';

That one step turns this finding from a question into an answer the next time the scan runs.

5. Give it a low privilege proxy if it uses CmdExec or PowerShell, rather than letting it run as the Agent service account.
6. Add failure notification, since a startup job failing silently after an unplanned restart is the usual way its absence goes unnoticed:

EXEC msdb.dbo.sp_update_job
     @job_name = N'Warm Buffer Pool',
     @notify_level_email = 2,
     @notify_email_operator_name = N'DBA Team';

7. Consider whether it needs to be at startup at all. A cache warming job is often better on a schedule that runs shortly after startup rather than during it, so it does not compete with recovery and reconnection.

Review the startup stored procedures too, which are the same idea by a different mechanism and are even easier to miss:

SELECT [name], OBJECTPROPERTY([object_id], 'ExecIsStartup') AS [is_startup]
  FROM sys.procedures WITH (NOLOCK)
 WHERE OBJECTPROPERTY([object_id], 'ExecIsStartup') = 1;

SELECT [name], [value_in_use] FROM sys.configurations WHERE [name] = 'scan for startup procs';

How long it takes

About half an hour to review and document. Investigating one you cannot account for takes as long as it takes.

Related reports

Related checks

Frequently asked questions

Is a startup job a problem in itself? No. It is a legitimate feature with real uses. The finding asks you to confirm that each one is yours and that you know what it does.

What should make me suspicious? A CmdExec or PowerShell step, a creation date that matches nothing you did, an owner who is not a DBA, an obfuscated or encoded command, or a job whose name is designed to look like a system job.

Can I stop startup jobs running during recovery? Move the work to a schedule that fires a few minutes after startup instead. That gets the same result without competing with recovery and the reconnection wave.

Does this apply on Amazon RDS? The check is skipped there, because Agent job configuration is constrained by the platform.