Report for Permissions Groups and Users

Question

Our tenant permissions groups have never been audited, and no one documented them or what their specific use was. This means that, 5 years after they were setup and the original admin is long gone, we are having people come to us asking why they don't have permission to work with specific resources, and I, their admin, cannot always give them a definitive reason, and I do not want to band-aid everything, not knowing if there is something already set up to solve that issue. However, each site seems to have this set of unique permissions groups and reason for being.

So, this leads to my request: I need to run a report in SharePoint that will give me a list of all Permissions Groups (they are unique for each site, apparently) and Users that are assigned to them, as well as a description of the Group. I would like it to be exportable to Excel (or CSV), if possible, and Iwould prefer to run it for all sites and subsites at one time. I just can't seem to find how to work it. I have found mention of a few scripts online, but I would prefer to run something natively, maybe through PowerShell native cmdlets, as I don't want anything that can't be trusted running around on the tenant. Truthfully, though, some idea of where to start from people who have done this kind of thing before would be a big help. Thank you, in advance!

Answer 1

I developed something for a similar purpose. This is not exactly what you are looking for but It can be modified for your need. The code is tested and run without any error. What the code does: You need to give the starting point(Site Url) The code Starts from a given SiteUrl goes to each subsite and each one fetches all the people have access to that subsite, type of permission and the name of the group they belong to.

the code generates a CSV File per URL and one CSV with All information.

function connect-site($webs,$creds){ 
Connect-PNPonline -Url $webs -Credentials $cred }  
function get-sitepermission($web,$cred){ 

$rec=@() #this Variable will hold the output Data
$ReportPerSite=@() #this Variable will hold the output Data
connect-site -webs $web -creds $cred 

if($web -eq $parentsitename){ 
    Write-Host "Fetching permission for" $web
    $Pgroups=Get-PNPGroup 
    foreach($Pgroup in $Pgroups){ 
        $DLGP = "" | Select "SiteUrl","GroupName","GroupMember","Permission"
        $pPerm=Get-PNPGroupPermissions -Identity $Pgroup.loginname -ErrorAction SilentlyContinue |Where-Object {$_.Hidden -like "False"} 
        if($pPerm -ne $null){ 
            $DLGP.SiteUrl=$web 
            $DLGP.GroupName=$Pgroup.loginname 
            $DLGP.Permission=$pPerm.Name 
            $GMembers = Get-PnPGroupMembers -Identity $DLGP.GroupName
            Write-Host "-----------" $DLGP.GroupName "-----------"
            foreach($member in $GMembers){
                Write-Host "+" $member.Title 
                $DLGP.GroupMember = $member.Title
                $rec+= $DLGP.PSObject.Copy()
                $ReportPerSite+=$DLGP.PSObject.Copy()
            }

        } 
    }
    $FileName ="MainSite"
    $reportPath = '{0}{1}.csv' -f $outputPath,$FileName 
    $ReportPerSite | Export-Csv -Path $reportPath
    Write-Host $web "permission fetched!" 
    Write-Host "----------------------------------------------------------------------------------"
} 
$subwebs=Get-PNPSubWebs 
foreach($subweb in $subwebs){ 
    $ReportPerSite=@()
    connect-site -webs $subweb.Url -creds $cred 
    Write-Host "Fetching permission for" $subweb.Url
    $DLGP = "" | Select "SiteUrl","GroupName","GroupMember","Permission" 
    $groups=Get-PNPGroup 
    foreach($group in $groups){ 
        $DLGP = "" | Select "SiteUrl","GroupName","GroupMember","Permission" 
        $sPerm=Get-PNPGroupPermissions -Identity $group.loginname -ErrorAction SilentlyContinue |Where-Object {$_.Hidden -like "False"} 
        if ($sPerm -ne $null){ 
            $DLGP.SiteUrl=$subweb.Url 
            $DLGP.GroupName=$group.loginname 
            $DLGP.Permission=$sPerm.Name 
            $GMembers = Get-PnPGroupMembers -Identity $DLGP.GroupName
            Write-Host "-----------" $DLGP.GroupName "-----------"
            foreach($member in $GMembers){
                Write-Host "+" $member.Title 
                $DLGP.GroupMember = $member.Title
                $rec+= $DLGP.PSObject.Copy()
                $ReportPerSite+=$DLGP.PSObject.Copy()
            }
        } 
    }
    $FileName =$subweb.Url
    [IO.Path]::GetinvalidFileNameChars() | ForEach-Object {$Filename = $Filename.Replace($_," ")}
    $reportPath = '{0}{1}.csv' -f $outputPath,$FileName 
    $ReportPerSite | Export-Csv -Path $reportPath

    Write-Host $subweb.Url "permission fetched!" 
    Write-Host "----------------------------------------------------------------------------------"
    get-sitepermission -web $subweb.Url -cred $cred 

} 
return $rec 
}
function Find-Folders {
[Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") | Out-Null
[System.Windows.Forms.Application]::EnableVisualStyles()
$browse = New-Object System.Windows.Forms.FolderBrowserDialog
$browse.SelectedPath = "C:\"
$browse.ShowNewFolderButton = $false
$browse.Description = "Please select a  directory To save user Privlage Audit report"

$loop = $true
while($loop)
{
    if ($browse.ShowDialog() -eq "OK")
    {
    $loop = $false

    #Insert your script here

    } else
    {
        $res = [System.Windows.Forms.MessageBox]::Show("You clicked Cancel. The program can not run unless you choose a valid location for output report.Would you like to try again?", "Select a location", [System.Windows.Forms.MessageBoxButtons]::RetryCancel)
        if($res -eq "Cancel")
        {
            #Ends script
            return $null
        }
    }
}
$browse.SelectedPath
$browse.Dispose()
} 

#Input parameter 
$cred=Get-Credential 
$parentsitename="https://yourcompany.sharepoint.com/startingpointSiteName" 
$outputPath= Find-Folders
$outputPath= '{0}\' -f $outputPath
if ($outputPath -eq $null){
Write-Host "You Didn't select any Output. The program is terminated!!"
}
else{
#Calling the function and Exporting TO CSV 
$Sitepermission = get-sitepermission -web $parentsitename -cred $cred 
Write-Host 'Exporting the report to CSV'
$FileName='FinalReport'
$reportPath = '{0}{1}.csv' -f $outputPath,$FileName
$Sitepermission |Export-Csv -Path $reportPath
Write-Host 'Report Generated You can Find it in the following path : ' 
$outputPath"Report.csv"
}

Just Note Several Point -You need to install PNP to used this script -This script Is just starting point -The script is extreamly slow (it takes about 40 minutes for 300sites) you might want to optimise the loops. -You need to change one line in code and put your starting url. -to test thescript start from a site which does not have any subsite. -When you run the script you will be asked to choose a location to save the reports and to give you credential(SP admin email and pasword)