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,
       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 

Tags: ,

Tech Tips

Comments (4) -

5/19/2011 11:03:20 AM #

rachael traub

this is awesome, but displays site collection owners. what about site owners?

rachael traub |

5/22/2011 8:32:04 AM #

Joey

If you run this on the Content DB, typically WSS_Content{_GUID}, you will be getting the sites.
Are you thinking of the documents?  In this case, there doesn't appear to be a concept of individual owners (other than the user who currently has it checked out).

Joey |

8/8/2011 3:49:51 PM #

evelyn rivera

how can i get it to show the top site only.  this shows top site and subsites.  thanks.

evelyn rivera |

8/10/2011 8:52:45 PM #

Joey

You could just tack:
WHERE ParentWebId is null
on the end.  That would give you the root sites.

Joey |

Comments are closed

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

Recommendations