1

I am trying to retrieve all the sites a particular user has access from the content database. But not able to get all the sites. Somehow I can get site collections where user has access but still not able to get the sub sites. Any leads please.

  • can you share your code? you want to get the all site collections and subsite where a particular user has access? – Waqas Sarwar MVP Jan 24 '18 at 14:19
  • Yes I am trying to get the same. I was trying it on sql itself, but not getting appropriate tables. – notorious Jan 24 '18 at 14:49
2

Directly querying the database is not supported and it put your farm at risk. So please avoid it.

You can run powershell script to get the same results. You can try the below script

Read the explanation over here:How to Determine Where a User Has Been Granted Access in SharePoint 2010

$ver = $host | select version 
if($Ver.version.major -gt 1) {$Host.Runspace.ThreadOptions = "ReuseThread"} 
if(!(Get-PSSnapin Microsoft.SharePoint.PowerShell -ea 0)) 
{ 
Add-PSSnapin Microsoft.SharePoint.PowerShell 
} 

## 
#Set Script Variables 
## 

$WebApplicationURL = "http://Contoso.com" 
$UnwantedDomainPrefix = "OldContoso" 
$LoggingDirectory = "C:\PermissionReport\" 

## 
#Load Functions 
## 

Function EnsureLoggingDirectory ($LoggingDirectory) 
{ 
    if(!(Test-Path $LoggingDirectory)) 
    { 
    $LoggingDirectory 
    Set-Variable -name Filename -Value ("$LoggingDirectory\SecurityReport_" +$StartTime +".txt") -Scope Script 
    Write-Host "Path " $LoggingDirectory " does not exist. `r`nCreating Directory" 
    New-Item -Path $LoggingDirectory -ItemType Directory 
    } 
} 

Function TrimDirectory ($LoggingDirectory) 
{ 
    if($LoggingDirectory.EndsWith("\")) 
    { 
        Set-Variable -Name LoggingDirectory -Value ($LoggingDirectory.Substring(0, ($LoggingDirectory.Length - 1))) -Scope Script 
    } 
} 

## 
#Start Script Execution 
## 
$StartTime = (Get-Date -UFormat "%Y-%m-%d_%I-%M-%S %p").tostring() 
TrimDirectory $LoggingDirectory 
EnsureLoggingDirectory $LoggingDirectory 
$Filename = "$LoggingDirectory\SecurityReport_" +$StartTime +".txt" 
"Security Report: $StartTime `r`n" | Out-File $Filename -Force 
"Finding Users Beginning with "  + $UnwantedDomainPrefix + "\ and NTAuthority" | Out-File $Filename -Append 

$AllSites = Get-SPSite -WebApplication $WebApplicationURL -Limit All 
foreach($Site in $AllSites) 
{ 
    $AllWebs = $Site.Allwebs 
    "`r`n`r`nSite: " +$Site.URL | Out-File $Filename -Append 
    foreach($Web in $AllWebs) 
    { 
        if($Web.HasUniqueRoleAssignments) 
        { 
            if($Web.isrootweb) 
            { 
                "`r`nweb '" + $Web.url + "' is Root Web and has unique permissions" | Out-File $Filename -Append 
            } 
            else 
            { 
                "`r`nWeb '" + $Web.url + "' is using unique permissions" | Out-File $Filename -Append 
            } 
            $AllLists = $Web.lists 
            $WebRoleAssignments = $Web.RoleAssignments 
                foreach($WebRoleAssignment in $WebRoleAssignments) 
                { 
                    if($WebRoleAssignment.member.userlogin) 
                    { 
                        if($WebRoleAssignment.Member.userlogin.split("\")[0] -eq $UnwantedDomainPrefix -or $WebRoleAssignment.Member.userlogin.split("\")[0] -eq "NT Authority") 
                        { 
                            "User '" + $WebRoleAssignment.Member + "' has been assigned '" + ($WebRoleAssignment.RoleDefinitionBindings | select name).name + "'" | Out-File $Filename -Append 
                        } 
                    } 
                    else 
                    { 
                        $allWebUsers = $WebRoleassignment.member.users 
                        foreach($WebUser in $AllWebUsers) 
                        { 
                            if($WebUser.userlogin.split("\")[0] -eq $UnwantedDomainPrefix -or $WebUser.userlogin.split("\")[0] -eq "NT Authority") 
                            { 
                                "User '" + $WebUser.Userlogin + "' has been added to the '" + $WebRoleAssignment.member.name + "' group" | Out-File $Filename -Append 
                            } 
                        } 
                    } 
                } 
            foreach($List in $AllLists) 
            { 
                if($List.HasUniqueRoleAssignments) 
                { 
                    "`r`nList '" + $List.title + "' is using unique permissions `r`nURL: " + $WebApplicationURL +$List.DefaultViewURL | Out-File $Filename -Append 
                    $RoleAssignments = $List.RoleAssignments 
                    foreach($RoleAssignment in $RoleAssignments) 
                    { 
                        if($RoleAssignment.member.userlogin) 
                        { 
                            if($RoleAssignment.Member.userlogin.split("\")[0] -eq $UnwantedDomainPrefix -or $RoleAssignment.Member.userlogin.split("\")[0] -eq "NT Authority") 
                            { 
                                "User '" + $RoleAssignment.Member + "' has been assigned '" + ($RoleAssignment.RoleDefinitionBindings | select name).name + "'" | Out-File $Filename -Append 
                            } 
                        } 
                        else 
                        { 
                            $allUsers = $Roleassignment.member.users 
                            foreach($User in $AllUsers) 
                            { 
                                if($user.userlogin.split("\")[0] -eq $UnwantedDomainPrefix -or $user.userlogin.split("\")[0] -eq "NT Authority") 
                                { 
                                    "User '" + $User.Userlogin + "' has been added to the '" + $RoleAssignment.member.name + "' group" | Out-File $Filename -Append 
                                } 
                            } 
                        } 
                    } 
                } 
            } 
        } 
    } 
} 
| improve this answer | |
  • Thanks. But this was the first approach that went for. Since it is a bit time consuming so i was trying to do something by directly querying the DB. – notorious Jan 24 '18 at 16:47
  • But DB approach is risky and not supported. read this article support.microsoft.com/en-us/help/841057/… read this "Read operations addendum" – Waqas Sarwar MVP Jan 24 '18 at 16:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.