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
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!
Hi Maruthachalam Krishnamurthy,
ReplyDeleteThanks 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
Hi Rudy Rulz,
ReplyDeleteThanks 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!
Hi,
ReplyDeleteI 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
Hi ShanthaLakshmi,
ReplyDeleteYes it is possible. You can use SQL query to retrieve the user permissions. Soon, I will post the SQL query for your requirement.
Hi ShanthaLakshmi,
ReplyDeleteI 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!
Hi Maru,
ReplyDeleteI 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.
Hi
DeleteThis 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!
Hello,
Deletea 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
Hi,
ReplyDeleteThank 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?
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.
Deletehttp://sharepoint-works.blogspot.com/2012/05/programmatically-get-sharepoint-users.html