locked
Query to retrieve users in the Owners group of s SharePoint Site RRS feed

  • Question

  • Hi people,

    Need help in writing a query that would retrieve the list of user's who are in the Owner's group of a team site!!

    I have tried a few options:

    1. SELECT WEbs.fullUrl, Webs.Title, grps.Title AS GRoupTitle, ui.tp_title, ui.tp_login FROM [dbo].Groups grps, [dbo].Webs webs, [dbo].userinfo ui, [dbo].groupmembership gm WHERE grps.SiteId = Webs.SiteId AND Webs.SiteId = gm.siteid AND Webs.siteid = ui.tp_siteid AND grps.title LIKE '%owner%' AND gm.memberid = ui.tp_id

    2. SELECT [dbo].Webs.FUllURL, [dbo].Groups.ID, [dbo].Groups.Title, [dbo].UserInfo.tp_Title, PROD_INTRANET_US4.[dbo].UserInfo.tp_Login FROM [dbo].GroupMembership INNER JOIN [dbo].Groups ON [dbo].GroupMembership.SiteId = [dbo].Groups.SiteId INNER JOIN [dbo].UserInfo ON [dbo].GroupMembership.MemberId = [dbo].UserInfo.tp_ID INNER JOIN [dbo].WEbs ON [dbo].groupmembership.siteid = [dbo].webs.siteid Where [dbo].Groups.title LIKE '%Owner%'

    But in both the case, I am getting users who are actually not there in the owner's group of a site (when I test).  Also, I get duplicate records and NTAUTHORITY\Authenticatedusers too!!!

    Hoping for some pointers.

    Cheers,

    Sumit

    Tuesday, March 1, 2011 5:25 AM

Answers

All replies

  • Sumit,

    Why not just use the API

    Sample code

    using (SPWeb web = SPContext.Current.Site.RootWeb)
    {
      SPGroup ownerGroup = web.SiteGroups["{replace with name of owners group}"];
      foreach(SPUser user in ownerGroup.Users)
      {
       //do someting
      }
    }
    

    Microsoft Certified Master | SharePoint 2007
    Blog
    Tuesday, March 1, 2011 5:56 AM
  • Hi Ram,

    Forgot to mention, I am supposed to be achieving this only using SQL queries.  No Object model :(

    Thanks for the response.

    Cheers,

    Sumit


    -- Sumit
    Tuesday, March 1, 2011 6:08 AM
  • Hey Guys,

    Found a blog. which gave out a very complex, but working query :)

    http://patrickboom.wordpress.com/2010/08/05/gathering-site-data-using-sql/

    Thanks Patrick.

    It did require a few modifications though.

    Cheers,

    Sumit

     


    -- Sumit
    Tuesday, March 1, 2011 8:39 AM