Thursday, November 19, 2009

SharePoint 2007 – FIVE very helpful (yet unsupported) stored procedures for querying membership data

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

jdoe@acmedomain.com

14

HR Team Member

20

Doe, Jane

acmedomain\jadoe

jadoe@acmedomain.com

14

HR Team Member

2

Smith, Bob

acmedomain\bsmith

bsmith@acmedomain.com

14

HR Team Member

24

Yamamoto, Kazue

acmedomain\kyamamoto

kyamamoto@acmedomain.com

14

HR Team Member

46

Nakamura, Ichiro

acmedomain\inakamura

inakamura@acmedomain.com

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

jdoe@acmedomain.com

14

HR Team Member

20

Doe, Jane

acmedomain\jadoe

jadoe@acmedomain.com

14

HR Team Member

2

Smith, Bob

acmedomain\bsmith

bsmith@acmedomain.com

14

HR Team Member

24

Yamamoto, Kazue

acmedomain\kyamamoto

kyamamoto@acmedomain.com

14

HR Team Member

46

Nakamura, Ichiro

acmedomain\inakamura

inakamura@acmedomain.com

14

HR Team Member

7

Horowitz, Rick

acmedomain\rhorowitz

rhorowitz@acmedomain.com

15

Acct Sr Managers

3

Davis, Jiro

acmedomain\jdavis

jdavis@acmedomain.com

15

Acct Sr Managers

No comments:

Post a Comment