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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy