The job broke when Dave left: getting SQL Agent job ownership and security right

#The incident

A job that worked for years fails overnight with:

The job failed. Unable to determine if the owner (CORP\dave.smith) of job Finance - ETL - Load GL has server access
(reason: Could not obtain information about Windows NT group/user 'CORP\dave.smith', error code 0x534.)

Dave's account was disabled when he left. SQL Agent checks the owner before every run. If the owner can't be validated, because the account is disabled or deleted, the domain controller can't be reached, or the login was dropped, the job doesn't start.

#Why the owner matters

So the owner controls both availability (can the job start?) and security (what can its steps do?).

#Recommended owner

• Use sa for jobs that DBAs manage. It always exists and is always sysadmin. It still works if you disable and rename sa (a common hardening step), because ownership is stored by SID (0x01).
• For application jobs that must not run as sysadmin, use a dedicated SQL or Windows login with only the needed rights, not a person. Use proxies for non-T-SQL steps.
• Never use personal accounts. People change roles and leave.

#Find risky owners

SELECT j.name AS job_name, SUSER_SNAME(j.owner_sid) AS owner,
       CASE WHEN SUSER_SNAME(j.owner_sid) IS NULL THEN 'ORPHANED (login not found)' END AS problem,
       IS_SRVROLEMEMBER('sysadmin', SUSER_SNAME(j.owner_sid)) AS owner_is_sysadmin,
       sp.is_disabled AS login_disabled
FROM msdb.dbo.sysjobs j
LEFT JOIN sys.server_principals sp ON sp.sid = j.owner_sid
WHERE j.owner_sid <> 0x01                -- not sa
ORDER BY owner, job_name;

Also check Windows logins that still exist in SQL Server but are disabled or deleted in Active Directory:

EXEC sys.sp_validatelogins;   -- lists Windows logins whose accounts no longer exist in AD

#Fix ownership

-- one job
EXEC msdb.dbo.sp_update_job @job_name = N'Finance - ETL - Load GL', @owner_login_name = N'sa';

-- every job owned by personal accounts (review the PRINT output first)
DECLARE @sql nvarchar(max) = N'';
SELECT @sql += N'EXEC msdb.dbo.sp_update_job @job_id = ''' + CAST(job_id AS nvarchar(36)) + N''', @owner_login_name = N''sa'';  -- '
             + name + N' (was ' + ISNULL(SUSER_SNAME(owner_sid), N'orphaned') + N')' + CHAR(10)
FROM msdb.dbo.sysjobs WHERE owner_sid <> 0x01;
PRINT @sql;
-- EXEC sys.sp_executesql @sql;

# dbatools: find and fix across many servers
Test-DbaAgentJobOwner -SqlInstance (Get-Content servers.txt) | Where-Object OwnerMatch -eq $false
Set-DbaAgentJobOwner -SqlInstance sql01 -Login sa

Warning

Changing a non-sysadmin owner to sa raises the job's T-SQL steps to the Agent service context, which is sysadmin-level. That's a security change. For application jobs, move to a dedicated, least-privileged login instead, and test that the steps still work.

#Ownership by people who aren't DBAs

Developers with SQLAgentUserRole can create jobs. Those jobs are owned by the developer and run as the developer. That's fine for dev, but in production:

• Move production jobs to a service owner as part of deployment.
• Script jobs from source control, with @owner_login_name set explicitly.

#Owner and maintenance plans

Maintenance plans are SSIS packages stored in msdb. Their jobs are owned by whoever created or last saved the plan. When that person leaves, the plan's jobs fail the same way. Fix the job owners and the plan owner:

-- who owns each maintenance plan
SELECT name, SUSER_SNAME(ownersid) AS owner FROM msdb.dbo.sysssispackages WHERE packagetype = 6;   -- 6 = maintenance plan

To change the plan owner, open the plan in SSMS while connected as the login that should own it (or sa) and save it. Then set the owner of the plan's subplan jobs with sp_update_job. Avoid editing sysssispackages directly.

#Prevent it next time

• Job template sets @owner_login_name = N'sa' or a service login.
• Weekly check: jobs not owned by sa or approved service logins.
• HR leaver process includes "check SQL Agent job ownership" for DBAs and developers.
• Policy-Based Management or a dbatools check in CI enforces the rule.