Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Domain users not in sys.server_principles Expand / Collapse
Author
Message
Posted Thursday, April 11, 2013 4:13 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 07, 2014 8:17 AM
Points: 323, Visits: 1,446
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? Is it some kind of Active Directory query or maybe sys.server_principles isn't the right table to query.

Thanks,
Ken
Post #1441511
Posted Thursday, April 11, 2013 8:45 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1441541
Posted Friday, April 12, 2013 10:02 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 07, 2014 8:17 AM
Points: 323, Visits: 1,446
Thanks a lot! SUSER_SNAME() is doing it.

Ken
Post #1441781
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse