Apr 7, 2012

Exporting SharePoint User Groups with Group Names

In the article Exporting SharePoint User Groups into Excel, we have learned about exporting the SharePoint user groups into excel. Here we are able to export all the users in a SharePoint site but we are unable to sort the users with the group name.
In this article, we will learn a small work around filter the users with the group name.
We can use SQL Query to fetch this data.
Connect to the SQL server and open a new query in the content database where your SharePoint site resides.
The SharePoint content DB does not expose the tables (for lists, libraries, users, etc.) in the database directly.
The below is the query which will fetch all the users in the SharePoint site with the user groups in a separate column in the result.
SELECT dbo.Groups.ID, dbo.Groups.Title, dbo.UserInfo.tp_Title, 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
Hope this helps you!
Please free to comment. Always your comments makes me to write more!

10 comments:

  1. Hi Maruthachalam Krishnamurthy,

    Thanks for this type of KT.
    But do we have an other way beside this because to run query we required the credentials of production server ......thats not possible all time ....... So please suggest any other option by which we can filter all users names as per the certain Group

    ReplyDelete
  2. Hi Rudy Rulz,
    Thanks for your comment!
    Is it possible for you to create a web app using Visual Studio, then add a grid view control and in the code behind run the query to get the user info displayed in the grid view control from the content DB ?
    I am suggesting this option since you donot have direct access to the SQL server.
    Hope this helps you!

    ReplyDelete
  3. Hi,

    I need a User name along with permission level of a particular child site to be export into excel sheet is it possible.Can u guide me.

    Thanks,
    Shantha

    ReplyDelete
  4. Hi ShanthaLakshmi,
    Yes it is possible. You can use SQL query to retrieve the user permissions. Soon, I will post the SQL query for your requirement.

    ReplyDelete
  5. Hi ShanthaLakshmi,
    I have been trying out different ways to retrieve the user permissions using SQL query. But I came through an article from MSDN which explains that the using of SQL queries directly content DB is not a good practice.

    http://msdn.microsoft.com/en-us/library/bb861829.aspx

    Hence, always Object model is preferable one! I have posted a console application which will retrieve the users with the Group Names separately. Have a look at this article.

    http://sharepoint-works.blogspot.com/2012/05/programmatically-get-sharepoint-users.html

    Hope this helps you!

    ReplyDelete
  6. Hi Maru,

    I just run your query as is and get as a result list of all users repeated will all groups on SP site ! Shown like all user are members of all groups! Actually it's not true. What did I wrong with query? How to view user with groups they are members actually ?

    My bests.

    ReplyDelete
    Replies
    1. Hi
      This technique will give the list of users in the SharePoint site but no duplicates. If you require the users with group name, read this article, which will help you fetching the users with group name.

      http://sharepoint-works.blogspot.com/2012/05/programmatically-get-sharepoint-users.html

      Hope this helps you!

      Delete
    2. Hello,
      a join criteria is missing. the correct request is :
      SELECT Groups.ID, Groups.Title, UserInfo.tp_Title, UserInfo.tp_Login
      FROM GroupMembership INNER JOIN Groups ON GroupMembership.SiteId = Groups.SiteId AND GroupMembership.GroupId = Groups.ID
      INNER JOIN UserInfo ON GroupMembership.MemberId = UserInfo.tp_ID

      Thanks
      Sylvain

      Delete
  7. Hi,
    Thank u Maruthachalam for ur blog about getting Groups Name and Users.I got all users from site but i need to import all user profile information from site to SQL SERVER DB directly.Is it possible?? Can u guide us?

    ReplyDelete
    Replies
    1. Yes it is possible. I will post the query Once I verify it and also you can have a look at this article which will give you user's information using object model.
      http://sharepoint-works.blogspot.com/2012/05/programmatically-get-sharepoint-users.html

      Delete

Dear Readers,

I LOVE to hear from you! Your feedback is always appreciated. I will try to reply to your query as soon as possible.

1. Make sure to click the "Notify me" check box at the right side to be notified of follow up comments and replies.
2. Please Do Not Spam - Spam comments will be deleted immediately upon review.