How to Generate a SharePoint Site and Subsite User Access Report Using PowerShell

Tuesday, June 07, 2022

How to Generate a SharePoint Site and Subsite User Access Report Using PowerShell

Description: Learn how to automate SharePoint user access auditing with PowerShell. Generate detailed CSV reports for sites and subsites to ensure security and compliance.

Managing a SharePoint environment is often like managing a growing city. At first, everything is organized, and you know exactly who has the keys to which building. But as the city expands—with new departments, projects, and hundreds of subsites—keeping track of "who has access to what" becomes a monumental task.

If you are a SharePoint Administrator, you’ve likely faced the "Audit Panic." Your manager or a compliance officer asks for a list of everyone who has access to the "Project Alpha" subsite and all its children. Doing this manually through the browser involves clicking into every subsite, checking permissions, and copying names into an Excel sheet. It is tedious, prone to human error, and, frankly, a waste of your technical expertise.

In this guide, we will look at a professional way to automate this process. We will use a PowerShell script designed for SharePoint Server to traverse your site collection and generate a clean, consolidated CSV report of user access.

The Challenge: Why Manual Permission Audits Fail

SharePoint permissions are powerful but complex. By default, a subsite inherits permissions from its parent. However, the moment a site owner clicks "Stop Inheriting Permissions" to add a specific group or user, that subsite becomes a "unique permission" island.

In a large site collection with dozens of subsites, these islands of unique permissions can lead to "permission creep," where users retain access to sensitive data long after they’ve left a project. A manual check usually misses these nuances. You need a way to:

  • Automatically identify which subsites have unique permissions.
  • Count how many users are inherited vs. explicitly assigned.
  • Get a bird's-eye view of the entire site collection hierarchy in one document.

Prerequisites: Getting Ready for Automation

Before we dive into the code, ensure you have the following environment set up:

SharePoint Management Shell: Make sure you are running it as Administrator.

Step-by-Step Logic: How the Script Works

The script we are using follows a logical flow that mimics what a human administrator would do, but at lightning speed. Here is the breakdown:

1. Connecting to the Site Collection

The script starts by taking your $siteUrl. It uses the Get-SPSite command to establish a connection to the root of your collection.

2. Establishing the "Parent" Baseline

To understand what is "unique," we first need to know what is "standard." The script fetches all users from the root site. These are the users that subsites would normally inherit if permissions weren't broken.

3. The Crawl (Looping through Subsites)

Using a foreach loop, the script visits every SPWeb (subsite) object. It doesn't matter if you have 5 subsites or 500; the script will visit each one.

4. The Unique Permission Check

This is the most critical part. The script checks the property HasUniqueRoleAssignments.

  • If it’s False, the subsite is just a mirror of the parent.
  • If it’s True, the script pauses to extract exactly who those unique users are.
5. Compiling and Exporting

Finally, the script packages this data into a PSCustomObject and exports it to a CSV file.

The PowerShell Script

Below is the structured code. You can copy this into a Notepad file and save it as SP-SubsiteUserReport.ps1.

# ==================================================================================
# Script Name: SP-SubsiteUserReport.ps1
# Description: Generates a CSV report of user access for SharePoint Sites & Subsites
# Author: Technical Admin
# ==================================================================================

# Add SharePoint Snapin if not already loaded
if ((Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null) {
    Add-PSSnapin Microsoft.SharePoint.PowerShell
}

# --- Configuration Variables ---
$siteUrl = "https://www.ms365.in"  # Change to your Site URL
$outputPath = "C:\Reports\UserAccessReport.csv" # Change to your desired path

# Connect to the Site Collection
$site = Get-SPSite $siteUrl
$results = @()

# Get Parent/Root Users for comparison
$rootWeb = $site.OpenWeb()
$parentUsers = $rootWeb.AllUsers | Select-Object -ExpandProperty UserLogin

Write-Host "Starting audit for: $siteUrl" -ForegroundColor Cyan

# Iterate through all subsites
foreach ($web in $site.AllWebs) {
    Write-Host "Processing: $($web.Url)" -ForegroundColor Yellow
    
    $isUnique = $web.HasUniqueRoleAssignments
    $customUsers = @()
    
    if ($isUnique) {
        # Fetch users with explicit permissions on this subsite
        $customUsers = $web.RoleAssignments | ForEach-Object { $_.Member } | Where-Object { $_.LoginName -ne $null }
    }

    # Create a custom data object for the report
    $reportItem = [PSCustomObject]@{
        WebTitle               = $web.Title
        WebUrl                 = $web.Url
        HasUniquePermissions   = $isUnique
        CustomUserCount        = if ($isUnique) { ($web.AllUsers.Count) } else { 0 }
        InheritedUserCount     = if (!$isUnique) { ($web.AllUsers.Count) } else { 0 }
        TotalUserCount         = $web.AllUsers.Count
    }

    $results += $reportItem
    $web.Dispose()
}

# Export the data to CSV
$results | Export-Csv -Path $outputPath -NoTypeInformation
$site.Dispose()

Write-Host "Report generated successfully at $outputPath" -ForegroundColor Green

Explaining the Code

  • $web.AllWebs: This ensures that even nested subsites (sub-subsites) are included.
  • $web.Dispose(): This is a vital best practice in SharePoint PowerShell. It clears the memory, preventing server RAM issues.
  • Export-Csv: This turns raw data into a format that Excel can read perfectly.

Understanding the CSV Report Columns

When you open the generated report in Excel, you will see several columns:

  1. WebTitle: The friendly name of the subsite (e.g., "Human Resources").
  2. WebUrl: The direct link to investigate suspicious permissions.
  3. HasUniquePermissions: If True, inheritance is broken. Audit these closely!
  4. CustomUserCount: People added specifically to this site.
  5. TotalUserCount: Total people who can log into this site.

Common Mistakes to Avoid

  • Running as a Regular User: Always use "Run as Administrator" on the SharePoint Management Shell.
  • Incorrect URL: Ensure $siteUrl starts with http:// or https://.
  • Memory Leaks: Always use .Dispose() on $web objects.
  • Output Path Errors: Ensure the folder (e.g., C:\Reports\) exists before running.

Best Practices for SharePoint Auditing

  • Schedule Your Audits: Run this report monthly or quarterly.
  • Run During Low-Traffic Hours: Crawling puts load on the SQL database.
  • Test in a Sandbox: Always test in a Dev/Staging environment first.
  • Combine Reports: Chain scripts together for a complete health check.

FAQ Section

Q1: Can I use this script for SharePoint Online (Microsoft 365)?

No, this uses SSOM for on-premises. For SharePoint Online, use PnP PowerShell.

Q2: Will this script show permissions of individual files or folders?

This focuses on Site and Subsite levels. Auditing every file can take hours and create massive reports.

Q3: Why does the script show "0" for CustomUserCount on some sites?

If HasUniquePermissions is False, it's just using the parent's list, so the count is 0.

Conclusion

Auditing SharePoint user access doesn't have to be a nightmare. By using PowerShell, you transform a five-hour manual task into a five-minute automated process. I encourage you to customize this script for your environment today!

Did you find this script helpful? Let me know in the comments below!

Tags