June 12, 2014 at 12:04 pm
I want to quickly identify SQL Agent Jobs that are owned by domain accounts which are individuals accounts rather than service accounts. Most of the instances we'll run this on are SQL 2008 R2.
Here is the query I came up with:
use msdb
select j.name, j.enabled, p.name as 'Owner', j.owner_sid
FROM sysjobs as j
left outer join sys.server_principals as p on j.owner_sid = p.sid
where p.name like 'DOMAIN NAME\%'
OR p.name is null
order by j.name
While some of the job owners domain accounts are identified, several come back with Owner as null, including jobs known to be owned by a person's domain account. The owner_sid for the job does not appear in sys.server_principals or sys.database_principals. But if I right-click the Job in SSMS and select properities the Owner field displays the owner's name.
Is there a different way I should be identifying the domain account name associated with the sid?
June 13, 2014 at 4:53 am
I think this happens when the owner of the job does not have an individual login to SQL Server but has access granted through the membership of a Domain Group which does have access. This is why they do not appear sys.server_principals.
You can use the suser_sname function to get the login names for these SIDs.
select j.name, j.owner_sid, isnull(p.name, suser_sname(j.owner_sid)) [Owner]
from msdb.dbo.sysjobs j
left outer join sys.server_principals p on j.owner_sid = p.sid
June 13, 2014 at 9:29 am
I knew there was a function to return the owner name of a sid. If only that was mentioned in the BOL articles on principals and sids I would have avoided developing an elaborate workaround.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply