locked
MOSS 2007 - List users with Full Control/Owner access in a SharePoint Web Application RRS feed

  • Question

  • 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.

    Thursday, July 26, 2012 5:16 AM

All replies

  • 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

    Wednesday, August 1, 2012 3:26 PM