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:
|
1 2 3 4 5 6 7 8 9 |
SELECT d.NAME , sp.NAME FROM sys.databases d LEFT JOIN sys.server_principals sp ON d.owner_sid = sp.sid; SELECT s.NAME , sp.NAME FROM msdb.dbo.sysjobs s LEFT OUTER JOIN sys.server_principals sp ON s.owner_sid = sp.sid ; |
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:
Continue reading on SirSQL.net.