January 26, 2010 at 8:55 am
The msdb.dbo.sysjobs table has a column in it, owner_sid. It's a varbinary(85) field and holds the active directory objectSID of the account that owns the job.
I need to know the name of the account.
I need to do this with a "minimum privileges" approach, not a "run stuff as sysadmin" approach.
I tried to join to the view master.sys.server_principals, but that view does not show all users unless you are running with sysadmin privileges.
I read somewhere that openquery is limited to 1000 returned rows and there are more users than that in the system, so I can't just query all users because the ones I want might not be in the first 1000 returned.
So, since I know the objectSId and want to know the sAMAccountName, I figured I would just ask active directory to give me the name that goes with the sid.
Here's a query that returns data:
SELECT *
FROM OPENQUERY(active_directory_linked_server_name
,'select sAMAccountName, objectSID from ''LDAP://DC=ab, DC=cd''
where objectCategory = ''user''
'
);
Let's say it returns an objectSID of 0x0102030405060708090A0B0C0D0E.
So, it seemed reasonable to be able to query using the sid value in the where clause:
SELECT *
FROM OPENQUERY(active_directory_linked_server_name
,'select sAMAccountName, objectSID from ''LDAP://DC=ab, DC=cd''
where objectCategory = ''user''
and objectSID = ''0x0102030405060708090A0B0C0D0E''
'
);
No data returned.
I know I may not be passing the objectSID back in "the right way", but I haven't figured out the magic handshake. That's assuming that there is one...
Basically, I'll be happy with pretty much any solution that does the following:
1) I know the SID.
2) I get the name of the account by using the SID.
3) It's written in t-sql.
4) It doesn't require the user running the query to have sysadmin privileges
January 26, 2010 at 9:28 am
Found a way simpler way to get this info. 🙂
Just grant execute on msdb.dbo.sqlagent_suser_sname to my low priv account and use it to get the acct name using the sid.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply