How do I? – Get a list of all users for each SharePoint site

Written by Cornelius J. van Dyk on . Posted in How Do I...

If you’re doing an upgrade of an existing SharePoint Portal Server 2003 farm to Microsoft Office SharePoint Server 2007 you will undoubtedly run into the need to identify all site administrators in your SPS 2003 environment. If for no other reason than to notify them that their Themes will be lost during the migration to MOSS 2007, you’ll be in contact with these administrators. The question is… how do you identify all the site administrators? If you only have a hand full of sites, it can certainly be done manually, but if you dealing with a huge farm and thousands of sites, this is not practical. That is exactly where TSQL comes to the rescue. Now as you know, we are not allowed to directly update any of the SharePoint databases as this action would invalidate your support agreement with Microsoft and put your portal in an unsupported state. That doesn’t mean we can’t read from it. This little SQL query will deliver the results you are looking for:  

USE SharePoint_SITE
SELECT w.fullurl, w.title, u.tp_title, u.tp_login
FROM webs w, userinfo u, webgroups g
INNER JOIN webgroupmembership p
ON g.webid = p.webid
AND g.id = p.groupid
WHERE g.type = 5
AND u.tp_id = p.memberid
AND u.tp_siteid = g.siteid
AND w.id = g.webid
AND w.siteid = g.siteid
ORDER BY w.fullurl
The WebGroups table defines all the web sites and they groups that was created for each. Joining it to the WebGroupMembership table provides us with the users that belong to the groups and from there we just have to get more user information and web site information from the UserInfo and Webs tables. If you wish to get a list of other standard user groups, just change the “g.type =” part of the statement as follows:
  1. Guest
  2. Reader
  3. Contributor
  4. Web Designer
  5. Administrator
Once you have the list, you can dump it to a .CSV file and manipulate it from Excel. You could also download my SQL script here.

Cheers
C




image

Tags: , ,

Trackback from your site.

Cornelius J. van Dyk

Born and raised in South Africa during the 70's I got my start in computers when a game on my Sinclair ZX Spectrum crashed, revealing it's BASIC source code. The ZX had a whopping 48K of memory which was considered to be a lot in the Commodore Vic20 era, but more importantly, it had BASIC built into the soft touch keyboard. Teaching myself to program, I coded my first commercial program at age 15.

After graduating high school at 17, I joined the South African Air Force, graduating the Academy and becoming a Pilot with the rank of First Lieutenant by age 20. After serving my country for six years, I made my way back into computer software.

Continuing my education, I graduated Suma Cum Laude from the Computer Training Institute before joining First National Bank where my work won the Smithsonian Award for Technological Innovation in the field of Banking and Insurance. Soon I met Will Coleman from Amdahl SA, who introduced me to a little known programming language named Huron/ObjectStar. As fate would have it, this unknown language and Y2K brought me to the USA in 1998.

I got involved with SharePoint after playing around with the Beta for SharePoint Portal Server 2003. Leaving my career at Rexnord to become a consultant in 2004, I was first awarded the Microsoft Most Valuable Professional Award for SharePoint in 2005, becoming only the 9th MVP for WSS at the time. I fulfilled a life long dream by pledging allegiance to the Flag as a US citizen in 2006. I met the love of my life and became a private consultant in 2008. I was honored to receive my ninth MVP award for SharePoint Server in 2013.

Leave a comment

You must be logged in to post a comment.