Found one very usefull query from internet :
http://johnlivingstontech.blogspot.com/2009/11/sharepoint-2007-five-very-helpful-yet.html
thanks for the creator to share this!! Below is the SQL query
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
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
Collapse