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,
sites.id AS siteid,
sites.ownerid,
owner.tp_login AS ownerlogin,
sites.secondarycontactid AS owner2id,
owner2.tp_login AS owner2login
FROM sites
INNER JOIN webs
ON sites.id = webs.siteid
LEFT OUTER JOIN userinfo AS owner2
ON sites.secondarycontactid = owner2.tp_id
AND sites.id = owner2.tp_siteid
LEFT OUTER JOIN userinfo AS owner
ON sites.ownerid = owner.tp_id
AND sites.id = owner.tp_siteid