SPNote

Finding Team Sites user can access is not easy in SharePoint 2003 but my company needs that feature cause users are very demanding... Anyways, it could be solved with querying right into the SharePoint Content Database but it's not good for the SharePoint and database as well.

I don't know how this query affect to SharePoint and the database but I recommend you not to use for applications cause it takes many resources and could cause SharePoint not working properly. I saw an artile about this but I don't have an url for now. Anyways, use these for information check only.

If you have any info on this, please share it to here.

1. Team Site Url by User ID or Group
   1. Query

      -- 1. Team Sites I can access using ID or Group
      SELECT w.FullUrl
      FROM UserInfo [u] WITH (NOLOCK)
      LEFT JOIN WebMembers [wm] WITH (NOLOCK) ON u.tp_ID = wm.UserID  
      LEFT JOIN Webs [w] WITH (NOLOCK) ON u.tp_SiteID = w.SiteID  
      LEFT JOIN WebGroupMemberShip [wgms] WITH (NOLOCK) 
          ON w.ID = wgms.WebID 
          AND u.tp_id = wgms.MemberID  
      LEFT JOIN WebGroups [wg] WITH (NOLOCK) 
          ON wgms.WebID = wg.WebID 
          AND wgms.GroupID = wg.ID  
      WHERE --wgms.MemberID IS NOT NULL AND 
          w.WebTemplate <> 21              -- except for personal sites
          AND w.FullUrl LIKE 'sites/%' 
          AND ( tp_Login = 'SPNOTE\nicole' OR tp_Login IN ('SPNOTE\bankershall2', 'SPNOTE\Domain Users') )
      GROUP BY w.FullUrl
      

   2. Query results
      
2. User Permission by User ID or Group
   1. Query

      --  Get permission info using ID or Group
      SELECT w.Title [Web Title], w.FullUrl, u.tp_Login, wg.Title [WebGroup Title]
      FROM UserInfo [u] WITH (NOLOCK)
      LEFT JOIN WebMembers [wm] WITH (NOLOCK) ON u.tp_ID = wm.UserID  
      LEFT JOIN Webs [w] WITH (NOLOCK) ON u.tp_SiteID = w.SiteID  
      LEFT JOIN WebGroupMemberShip [wgms] WITH (NOLOCK) 
          ON w.ID = wgms.WebID 
          AND u.tp_id = wgms.MemberID  
      LEFT JOIN WebGroups [wg] WITH (NOLOCK) 
          ON wgms.WebID = wg.WebID 
          AND wgms.GroupID = wg.ID  
      WHERE --wgms.MemberID IS NOT NULL AND 
          w.WebTemplate <> 21              -- except for personal sites
          AND w.FullUrl = 'sites/teamsite2'
          AND ( tp_Login = 'SPNOTE\nicole' OR tp_Login IN ('SPNOTE\bankershall2', 'SPNOTE\Domain Users') )
      GROUP BY w.FullUrl,u.tp_Login,wg.Title, w.Title
      

   2. Query results
      
   3. user.aspx
      
3. All Team Site Users by Team Site Url
   1. Query

   -- Get All Team Site Users by Team Site Url
   SELECT 
   	tp_Login [Login]
   	, tp_Title [Title]
   	, tp_Email [Email]
   	, CASE
   		WHEN LEN(wg.Title) > 0 THEN wg.Title
   		ELSE ''
   	END [Group Title]
   	, tp_DomainGroup [Domain Group]
   	, CASE
   		WHEN wgms.MemberID IS NULL THEN 'Group'
   		ELSE 'Direct'
   	END [Direct or From group]
   FROM UserInfo [u] WITH (NOLOCK)
   LEFT JOIN Webs [w] WITH (NOLOCK) 
   	ON u.tp_SiteID = w.SiteID
   LEFT JOIN WebGroupMemberShip [wgms] WITH (NOLOCK) 
   	ON w.ID = wgms.WebID
   	AND u.tp_id = wgms.MemberID
   LEFT JOIN WebGroups [wg] WITH (NOLOCK) 
       ON wgms.WebID = wg.WebID 
       AND wgms.GroupID = wg.ID
   WHERE w.FullUrl = 'sites/teamsite2'
       AND wg.WebID IS NOT NULL
   ORDER BY wgms.MemberID DESC
   

   
   
   2. Query results
      
   3. user.aspx
      same as above one