How does Management Studio Know Job Owner?

Question

How does Management Studio Know Job Owner?

Paul Bezanson-322376

SSC Journeyman

Points: 95
More actions
January 25, 2010 at 1:19 pm

#219852

When I open the properties dialog of a job in Management Studio, the owner of the job appears in the "Owner" edit box. This seems trivial enough since we know that the msdb..sysjobs table contains the "owner_sid" which links to the "sid" column in the master.sys.syslogs view. It starts to get a little spooky, though, when Management Studio magically displays the owner even when the owner DOESN'T appear in the master.sys.syslogins view!
This can happen when a user (say Dom\User) is granted permissions through membership in an active directory group (DomGrp) but doesn't have an explicit login in SQL Server. For the sake of illustration, say the Windows group (Dom\DomGrp) has been granted SysAdmin permissions in SQL Server. In this case Dom\User also has SysAdmin permissions even though he has no login of his own and does NOT appear in master.sys.syslogins.
Yet Management Studio, knows that Dom\User owns the job and displays "Dom\User" in the "Owner" edit box!
So, where does Management Studio get this information?
Thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Have you looked in sys.server_principals and sys.sql_logins?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Paul Bezanson-322376 SSC Journeyman Points: 95 More actions · Answer 2

Yes, I have looked in sys.server_principals and sys.sql_logins and neither of those views seem to hold the answer to this riddle; which is one of the reasons this is so fascinating to me. It must be available somewhere or somehow, but as yet, I've not been able to uncover the secret hiding place.

Thanks for your post.

george sibbald SSC Guru Points: 104210 More actions · Answer 3

If you logon to SQL using windows authentication SQL knows your individual windows account at that point. so SQL must be able to use windows based code to connect to a domain controller and confirm your credentials and check group memberships, and decide whether to let you logon or not.

I don't understand exactly how it does that but this article (even though old and 2000 based) explains all you really need to know

http://msdn.microsoft.com/en-us/library/aa905171(SQL.80).aspx.

you really need to talk to your AD guy.

---------------------------------------------------------------------

Paul Bezanson-322376 SSC Journeyman Points: 95 More actions · Answer 4

Thanks for the KB reference.

My question isn't so much about how users are authenticated, though, but more about how SQL knows who "owns" the job even though the owner isn't available in the usual places.

The msdb..sysjobs table includes the SID of the job's owner and the usual method is to use that SID to join to master.sys.syslogins (or master.sys.server_principals) to get the name of the job owner. But what happens when neither of those views contain the job's owner? (And this is by design, BTW.)

Management studio, however, knows how to find this information because it will acurately list the name of the owner even though that owner is not present in either of the above mentioned views. And that's my question... Where does SQL Server keep the owner name that Managment Studio is displaying?

Thanks again for your reply.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

Because SQL Server has the SID - it can use that SID to perform an AD lookup of the username. It gets the actual name from AD and does not need to look in it's own tables.

That is the whole purpose of using the SID - so we don't have to keep the external value stored in the database engine.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 6

I have a feeling it is returned via a dll, sp_helpjob ends up calling xp_sqlagent_enum_jobs which in turn calls xpstar.dll, can't see what that does, but I wouldn't be surprised if EXEC xp_logininfo sp was involved.

Andrew

Paul Bezanson-322376 SSC Journeyman Points: 95 More actions · Answer 7

Thanks, Jeffrey.

I think I agree with you that the name is probably coming from outside of SQL Server and your explanation makes a kind of Microsoft sense. 🙂

Thanks, again.

Paul Bezanson-322376 SSC Journeyman Points: 95 More actions · Answer 8

I took a look at the sp_help_job procedure by listing its text with sp_helptext but I don't see a call to the xp_sqlagent_enum_jobs procedure. But other references make the same comment so I'm sure I've just overlooked something.

As I mentioned to Jeffrey, all indications are that Management Studio is getting the information from somewhere outside of SQL Server... which hardly seems sporting, but at least I know in which direction to look now. 🙂

Thanks.

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 9

Had another look and it is returned via select suser_sname(owner_sid) from msdb.dbo.sysjobs, not as complicated as I thought.

Andrew

Paul Bezanson-322376 SSC Journeyman Points: 95 More actions · Answer 10

Son-of-a-gun! You're right!

I thought I looked at this function prior to submitting this forum entry, but in retracing my steps, it appears I looked at the "suser_name()" function instead. The "suser_name()" function only returns names that are present in the syslogins table -- which didn't help me.

That should teach me to be a little more careful with my spelling. (But it probably won't). 🙂

Thanks Andrew. Mystery solved.

Thanks also to all who contributed.