Checking User Access to SharePoint Site Collections

In this article, we will discuss how to connect to SharePoint using PowerShell, check if a specified user has access to all site collections, and export the results to a CSV file.

Prerequisites

• You need to have SharePoint Online Management Shell installed.
• You must have appropriate permissions to access the SharePoint sites and user information.

PowerShell Script Overview

The following script connects to SharePoint, retrieves all site collections, checks the specified user’s permissions, and exports the results to a CSV file.

PowerShell Script 1

# Connect to SharePoint
Connect-SPOService -Url https://contoso-admin.sharepoint.com

# Set the user's email address
$userEmail = "user@example.com"

# Create an array to hold the results
$results = @()

# Get all site collections in the tenant
$sites = Get-SPOSite -Limit All

# Loop through each collection and check if the user has access
foreach ($site in $sites) {
    # Get the user's permissions for the site collection
    $permissions = Get-SPOUser -Site $site.Url -LoginName $userEmail -ErrorAction SilentlyContinue
    if ($permissions) {
        # If the user has permissions, output the site collection URL
        Write-Host "User $($userEmail) has access to $($site.Url)"
        
        # Add the result to the results array
        $results += [PSCustomObject]@{
            UserEmail = $userEmail
            SiteUrl   = $site.Url
            HasAccess  = $true
        }
    } else {
        # If the user does not have permissions, you could note that as well
        $results += [PSCustomObject]@{
            UserEmail = $userEmail
            SiteUrl   = $site.Url
            HasAccess  = $false
        }
    }
}

# Define the output CSV file path
$outputPath = "C:\Path\To\Your\Output\SharePointUserAccess.csv"

# Export the results to a CSV file
$results | Export-Csv -Path $outputPath -NoTypeInformation

Write-Host "User access information exported to $outputPath"

PowerShell Script 2

# Connect to SharePoint
Connect-SPOService -Url https://contoso-admin.sharepoint.com

# Set the user's email address
$userEmail = "user@example.com"

# Get all site collections in the tenant
$sites = Get-SPOSite -Limit All

# Initialize an array to hold results
$results = @()

# Loop through each collection and check if the user has access
foreach ($site in $sites) {

    # Get the user's permissions for the site collection
    $permissions = Get-SPOUser -Site $site.Url -LoginName $userEmail -ErrorAction SilentlyContinue
    if ($permissions) {

        # If the user has permissions, create an object for the CSV
        $result = [PSCustomObject]@{
            User              = $userEmail
            SiteCollectionURL = $site.Url
            Permissions       = $permissions | Select-Object -ExpandProperty Permissions
        }

        # Add the result object to the results array
        $results += $result
        
        # Output to console (optional)
        Write-Host "User $($userEmail) has access to $($site.Url)"
    }
}

# Export the results array to a CSV file
$results | Export-Csv -Path "UserPermissionsReport.csv" -NoTypeInformation -Encoding UTF8

Write-Host "Exported results to UserPermissionsReport.csv"

Powershell Script 3

# Connect to SharePoint
Connect-SPOService -Url https://contoso-admin.sharepoint.com

# Set the user's email address
$userEmail = "user@example.com"

# Get all site collections in the tenant
$sites = Get-SPOSite -Limit All

# Create an array to hold the results
$results = @()

# Loop through each collection and check if the user has access
foreach ($site in $sites) {
    # Get the user's permissions for the site collection
    $permissions = Get-SPOUser -Site $site.Url -LoginName $userEmail -ErrorAction SilentlyContinue
    if ($permissions) {
        # If the user has permissions, store the site collection URL
        $result = [PSCustomObject]@{
            UserEmail         = $userEmail
            SiteCollectionUrl = $site.Url
            Permissions       = $permissions.RoleAssignments | ForEach-Object { $_.RoleDefinitionBindings.Name } -join ', '
        }
        $results += $result
    }
}

# Export results to a CSV file
$csvFilePath = "C:\Path\To\Your\Output.csv"
$results | Export-Csv -Path $csvFilePath -NoTypeInformation

Write-Host "Export completed. Results saved to $csvFilePath"

Explanation of the Script

This script can be broken down into several key sections:

• Connecting to SharePoint: The script connects to your SharePoint admin site using the Connect-SPOService cmdlet.
• User Email: Set the variable $userEmail to the email address of the user you want to check.
• Result Storage: An array named $results is created to keep track of the user access checks.
• Loop Through Site Collections: The script retrieves all site collections using Get-SPOSite and checks the user’s permissions using Get Get-SPOUser.
• If the user has access, the site URL is recorded alongside the user’s email, and the access status is set to true. If not, the access status is set to false.
• Exporting to CSV: Finally, the results are exported to a CSV file using the Export-Csv cmdlet, which allows you to easily view the data in spreadsheet applications.

Customizing the Script

You may want to customize the script according to your needs:

• Change the $userEmail variable to target a different user.
• Adjust the $outputPath variable to specify where you want to save the CSV file.
• You can also modify the output by adding more properties to the custom object in the results array.

Note: Ensure that the script is run in an environment with the appropriate permissions. The PowerShell session should have the necessary modules imported and the user should have adequate rights to access the SharePoint resources.

Conclusion

This PowerShell script provides a straightforward way to automate the process of checking user access across multiple SharePoint site collections. By exporting results to a CSV file, it offers a clear view of access permissions that can be useful for administrators in managing SharePoint environments.