Domain users not in sys.server_principles

  • 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

  • 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

  • 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