Who Owns Your Databases And Jobs?

SQL Server Best Practices
SQL Server Best Practices
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:

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.

54321
(0 votes. Average 0 of 5)
Leave a reply

Your email address will not be published. Required fields are marked *