Hello. I'm trying to find out who owns SQL Agent jobs on one of our servers where there's a lot of jobs.
SELECT sj.name AS 'job', sp.NAME AS 'owner', spr.NAME AS 'proxy', sj.*
FROM msdb..sysjobs sj
LEFT JOIN sys.server_principals sp ON sj.owner_sid=sp.sid
LEFT JOIN msdb..sysjobsteps sjt ON sj.job_id = sjt.job_id
LEFT JOIN msdb..sysproxies spr ON spr.proxy_id=sjt.proxy_id
A lot of these come back with NULL as the owner; most likely because that user has their access thru a domain group rather than having their individual login defined on the server. If you look at 1 of the jobs with no owner in the GUI it will display. And there is a valid looking owner_sid in sysjobs for these entries.
So how is the GUI getting it right?
Is it some kind of Active Directory query or maybe sys.server_principles isn't the right table to query.