MOSS 2007 - List users with Full Control/Owner access in a SharePoint Web Application
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.
July 26th, 2012 1:16am
Create an Executable that iterates through all the site collection in your web application. You can save this information in a list and expose it to the helpdesk. Otherwise send them an email with the report.
not sure why you are using SQL queries? The SPObjectmodel is powerful enough to get you the required information. Detailed owner information with information like email address etc can be sent to the helpdesk on daily\weekly\monthly basis.
Varun Saxena
Free Windows Admin Tool Kit Click here and download it now
August 1st, 2012 11:26am
This topic is archived. No further replies will be accepted.
Other recent topics
