Quick question, who owns your databases? And how about your jobs? Don’t know? Go find out…now find out without using the GUI.
Querying sys.databases will give you the owner_sid. The trouble is that you have to relate that owner_sid back to a user. The same with sysjobs, that sid needs to relate to someone.
You could attempt joining against sys.server_principals, however if there’s a user that doesn’t exist or is a part of a domain group they wouldn’t show up and you would just get a NULL returned. Try the following example:
Now what, how can you quickly and easily get the owner? A little known system function will come to the rescue.
SUSER_SNAME (server_user_sid) will return the login name associated with a SID. Using this you can very quickly grab the information about who owns what without having to play with joins or get incomplete results:
There’s some great little hidden gems lying around in SQL Server like this. You just need to find them.