I do quite a bit of SharePoint development and have always relied on the object model or web services. Unfortunately, I have ran into situations where using either option was less than optimal because obtaining the data I needed might cause performance problems since they required me to get recursive data. One of these situations is pulling membership data out of SharePoint for reporting or authentication purposes. Some of these examples were gleaned off of other examples I found on the web, but were cleaned up or added additional information. In all cases I have ensured that they on SELECT data, and always use NOLOCK for all tables.
1. Select Groups By URL
USE [WSS_Content]
CREATE PROCEDURE [dbo].[usp_SelectGroupsByUrl]
@url NVARCHAR(255)
AS
BEGIN
SELECT Groups.ID AS "GroupID", Groups.Title AS "GroupTitle", Webs.Title AS WebTitle, Webs.FullURL AS WebURL, Roles.Title AS "RoleTitle"
FROM RoleAssignment WITH (NOLOCK)
INNER JOIN Roles WITH (NOLOCK) ON Roles.SiteId = RoleAssignment.SiteId
AND Roles.RoleId = RoleAssignment.RoleId
INNER JOIN Groups WITH (NOLOCK) ON Groups.SiteId = RoleAssignment.SiteId
AND Groups.ID = RoleAssignment.PrincipalId
INNER JOIN Sites WITH (NOLOCK) ON Sites.Id = RoleAssignment.SiteId
INNER JOIN Perms WITH (NOLOCK) ON Perms.SiteId = RoleAssignment.SiteId
AND Perms.ScopeId = RoleAssignment.ScopeId
INNER JOIN Webs WITH (NOLOCK) ON Webs.Id = Perms.WebId
WHERE ((Webs.FullUrl = @url) OR (@url IS NULL))
ORDER BY
Groups.Title, Webs.Title, Perms.ScopeUrl
END
Sample Query
EXEC usp_SelectGroupsByUrl 'Sites/HR/Pay'
Results
| GroupID | GroupTitle | WebTitle | WebURL | RoleTitle |
| 71 | Manager | Accounting | Sites/HR/PAY | View Only |
| 74 | Acct Sr Manager | Accounting | Sites/HR/PAY | Full Control |
| 75 | Member | Accounting | Sites/HR/PAY | View Only |
| 76 | Viewer | Accounting | Sites/HR/PAY | View Only |
2. Select Groups By URL and User
USE [WSS_Content]
CREATE PROCEDURE [dbo].[usp_SelectGroupsByUrlUser]
@login NVARCHAR(255),
@url NVARCHAR(255)
AS
BEGIN
SELECT G.ID AS "GroupID", G.Title AS "GroupTitle", W.Title AS WebTitle, W.FullURL AS WebURL, R.Title AS "RoleTitle"
FROM RoleAssignment AS RA WITH (NOLOCK)
INNER JOIN Roles AS R WITH (NOLOCK) ON R.SiteId = RA.SiteId
AND R.RoleId = RA.RoleId
INNER JOIN Groups AS G WITH (NOLOCK) ON G.SiteId = RA.SiteId
AND G.ID = RA.PrincipalId
INNER JOIN Sites AS S WITH (NOLOCK) ON S.Id = RA.SiteId
INNER JOIN Perms AS P WITH (NOLOCK) ON P.SiteId = RA.SiteId
AND P.ScopeId = RA.ScopeId
INNER JOIN Webs AS W WITH (NOLOCK) ON W.Id = P.WebId
WHERE ((W.FullUrl = @url) OR (@url IS NULL))
AND G.ID IN
(
SELECT Groups.ID FROM GroupMemberShip WITH (NOLOCK)
INNER JOIN Groups ON GroupMembership.GroupID = Groups.ID
INNER JOIN UserInfo ON GroupMembership.MemberID = UserInfo.tp_ID
WHERE ((tp_Login = @login) OR (@login IS NULL))
)
ORDER BY
G.Title, W.Title, P.ScopeUrl
END
Sample Query
EXEC usp_SelectGroupsByUrlUser 'acmedomain\jdoe', 'Sites/HR/Pay'
Results
| GroupID | GroupTitle | WebTitle | WebURL | RoleTitle |
| 74 | Sr Manager | Accounting | Sites/HR/PAY | Full Control |
3. Select Groups By User Login
USE [WSS_Content]
CREATE PROCEDURE [dbo].[usp_SelectGroupsByUserLogin]
@login NVARCHAR(255)
AS
BEGIN
SELECT DISTINCT Groups.ID AS "GroupID", Groups.Title AS "GroupTitle"
FROM GroupMemberShip WITH (NOLOCK)
INNER JOIN Groups WITH (NOLOCK) ON GroupMembership.GroupID = Groups.ID
INNER JOIN UserInfo WITH (NOLOCK) ON GroupMembership.MemberID = UserInfo.tp_ID
WHERE ((tp_Login = @login) OR (@login IS NULL))
ORDER BY Groups.Title
END
Sample Query
EXEC usp_SelectGroupsByUserLogin 'acmedomain\jdoe'
Results
| GroupID | GroupTitle |
| 74 | Sr Manager |
| 52 | Corporate Viewer |
| 14 | HR Team Member |
4. Select Users By Group
USE [WSS_Content]
CREATE PROCEDURE [dbo].[usp_SelectUsersByGroup]
@groupTitle NVARCHAR(255)
AS
BEGIN
SELECT dbo.UserInfo.tp_ID AS UserID, dbo.UserInfo.tp_Title AS UserTitle, dbo.UserInfo.tp_Login AS UserLogin, dbo.UserInfo.tp_Email AS UserEmail, dbo.Groups.ID AS GroupsID, dbo.Groups.Title AS GroupsTitle
FROM UserInfo WITH (NOLOCK)
INNER JOIN GroupMembership WITH (NOLOCK) ON UserInfo.tp_ID = GroupMembership.MemberID
INNER JOIN Groups WITH (NOLOCK) ON GroupMembership.GroupID = Groups.ID
WHERE ((dbo.Groups.Title = @groupTitle) OR (@groupTitle IS NULL))
ORDER by dbo.UserInfo.tp_Login
END
Sample Query
EXEC usp_SelectUsersByGroup 'HR Team Member'
Results
| UserID | UserTitle | UserLogin | UserEmail | GroupsID | GroupsTitle |
| 23 | Doe, John | acmedomain\jdoe | 14 | HR Team Member | |
| 20 | Doe, Jane | acmedomain\jadoe | 14 | HR Team Member | |
| 2 | Smith, Bob | acmedomain\bsmith | 14 | HR Team Member | |
| 24 | Yamamoto, Kazue | acmedomain\kyamamoto | 14 | HR Team Member | |
| 46 | Nakamura, Ichiro | acmedomain\inakamura | 14 | HR Team Member |
5 Select Users By Groups
USE [WSS_Content]
CREATE PROCEDURE [dbo].[usp_SelectUsersByGroups]
@groupTitles VARCHAR(1000)
AS
BEGIN
DECLARE @idoc INT
EXEC sp_xml_preparedocument @idoc OUTPUT, @groupTitles
SELECT dbo.UserInfo.tp_ID AS UserID, dbo.UserInfo.tp_Title AS UserTitle, dbo.UserInfo.tp_Login AS UserLogin, dbo.UserInfo.tp_Email AS UserEmail, Groups.Title AS GroupTitle
FROM UserInfo WITH (NOLOCK)
INNER JOIN GroupMembership WITH (NOLOCK) ON UserInfo.tp_ID = GroupMembership.MemberID
INNER JOIN Groups WITH (NOLOCK) ON GroupMembership.GroupID = Groups.ID
JOIN OPENXML(@idoc, '/Root/Group', 0) WITH (Name VARCHAR(100)) AS g
ON dbo.Groups.Title = g.Name
ORDER by dbo.Groups.Title, dbo.UserInfo.tp_Title
END
Sample Query
EXEC usp_SelectUsersByGroups '<Root><Group Name="HR Team Member"></Group><Group Name="Acct Sr Managers"></Group></Root>'
Results
| UserID | UserTitle | UserLogin | UserEmail | GroupsID | GroupsTitle |
| 23 | Doe, John | acmedomain\jdoe | 14 | HR Team Member | |
| 20 | Doe, Jane | acmedomain\jadoe | 14 | HR Team Member | |
| 2 | Smith, Bob | acmedomain\bsmith | 14 | HR Team Member | |
| 24 | Yamamoto, Kazue | acmedomain\kyamamoto | 14 | HR Team Member | |
| 46 | Nakamura, Ichiro | acmedomain\inakamura | 14 | HR Team Member | |
| 7 | Horowitz, Rick | acmedomain\rhorowitz | 15 | Acct Sr Managers | |
| 3 | Davis, Jiro | acmedomain\jdavis | 15 | Acct Sr Managers |
