Our service desk does not (is not allowed to) have the necessary permissions to view SharePoint permissions, as such I am developing a simple web page for our service desk to search for users
have Owner/Full Control permission to a SharePoint object (list, page, site, etc).
I can do this produce the data easily enough for a single content DB using the below SQL script however I am having troubles finding a way to do this for an entire SharePoint Web Application.
SELECT
*
FROM (
--Search Account with FC and Owners permissions.
SELECT
distinct((CONVERT(VARCHAR(MAX),dbo.Perms.ScopeId))+'-'+'NTA'+(CONVERT(VARCHAR(MAX),dbo.UserInfo.tp_ID)))
AS PiD,
'<<ApplicationURL>>'+dbo.Perms.ScopeUrl
AS URL,
dbo
.UserInfo.tp_Title
AS Title,
'NT Account'
AS ObjectType,
dbo
.UserInfo.tp_Login
AS Logon
FROM dbo.RoleAssignment
WITH(NOLOCK)
INNER
JOIN dbo.Perms
WITH(NOLOCK)
ON dbo.RoleAssignment.ScopeId=dbo.Perms.ScopeId
INNER
JOIN dbo.UserInfo
WITH(NOLOCK)
ON dbo.RoleAssignment.PrincipalId=dbo.UserInfo.tp_ID
WHERE
(dbo.RoleAssignment.RoleId
= '1073741829')
OR
(dbo.RoleAssignment.RoleId
= '1073741928')
UNION
--Search for SharePoint groups with FC and Owner permissions.
SELECT
distinct((CONVERT(VARCHAR(MAX),dbo.Perms.ScopeId))+'-'+'SPG'+(CONVERT(VARCHAR(MAX),dbo.Groups.ID)))
AS PiD,
'<<ApplicationURL>>'+dbo.Perms.ScopeUrl
AS URL,
dbo
.Groups.Title
AS Title,
'SharePoint Group'
AS ObjectType,
NULL
AS Logon
FROM dbo.RoleAssignment
WITH(NOLOCK)
INNER
JOIN dbo.Perms
WITH(NOLOCK)
ON dbo.RoleAssignment.ScopeId=dbo.Perms.ScopeId
INNER
JOIN dbo.Groups
WITH(NOLOCK)
ON dbo.RoleAssignment.PrincipalId=dbo.Groups.ID
WHERE
(dbo.RoleAssignment.RoleId
= '1073741829'
OR dbo.RoleAssignment.RoleId
= '1073741928')
)
un
ORDER
BY URL
ASC
I have found solutions for SharePoint 2010 using powershell but nothing for 2007, any help would be greatly appreciated.
