List SharePoint site owners

by Joe Havelick 6. April 2010 11:21

I recently had some issues with site owners on my SharePoint sites.  Specifically, one of the sites didn't have one altogether.  The frontend and command line tools all reported "User could not be found".  This also derived from the error "You cannot delete the owners of a Web site collection" when attempting to remove a user for a dead account. 

If you find yourself in that predicament, the following SQL query can be run on any of the content databases to determine who (if anyone) is currently the owner or secondary owner of each site:

SELECT webs.fullurl,
       webs.title,                 AS siteid,
       owner.tp_login           AS ownerlogin,
       sites.secondarycontactid AS owner2id,
       owner2.tp_login          AS owner2login
FROM   sites
       INNER JOIN webs
         ON = webs.siteid
       LEFT OUTER JOIN userinfo AS owner2
         ON sites.secondarycontactid = owner2.tp_id
            AND = owner2.tp_siteid
       LEFT OUTER JOIN userinfo AS owner
         ON sites.ownerid = owner.tp_id
            AND = owner.tp_siteid 

Tags: ,

Tech Tips

About Me

Joe Havelick is a reasonable facsimile of this photo.

profile for Joe on Stack Exchange, a network of free, community-driven Q&A sites