April 11, 2013 at 4:13 pm
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
WHERE sj.enabled=1
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? :ermm: Is it some kind of Active Directory query or maybe sys.server_principles isn't the right table to query.
Thanks,
Ken
April 11, 2013 at 8:45 pm
You cannot rely on sys.server_principals to resolve the names of job owners that are entering the instance via a Windows Group. Use SUSER_SNAME() instead:
SELECT sj.name AS 'job',
SUSER_SNAME(owner_sid) AS 'owner',
spr.NAME AS 'proxy',
sj.*
FROM msdb.dbo.sysjobs sj
LEFT JOIN msdb.dbo.sysjobsteps sjt ON sj.job_id = sjt.job_id
LEFT JOIN msdb.dbo.sysproxies spr ON spr.proxy_id = sjt.proxy_id
WHERE sj.enabled = 1
If the column is still NULL using SUSER_SNAME() then the SID that owns the job is no longer linked to a valid Windows User, i.e. they were deleted from a machine or domain.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 12, 2013 at 10:02 am
Thanks a lot! SUSER_SNAME() is doing it.
Ken
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply