Export SharePoint Permissions To CSV With PnP PowerShell

by Andrew McMorgan 57 views

Hey guys! So, you're looking to get a handle on the permissions for your SharePoint site and want to export them to a nice, neat CSV file? Smart move! Dealing with permissions manually can be a total headache, especially in larger sites. Thankfully, with the power of PnP PowerShell, this task becomes way more manageable. We're going to dive deep into how you can achieve this, making your life a whole lot easier. Whether you're an admin trying to audit access or just curious about who has what permissions, this guide is for you.

Why Export SharePoint Permissions?

First off, why bother exporting permissions? Well, there are a bunch of solid reasons, guys. Auditing and Compliance are huge. In many organizations, you're required to regularly audit who has access to what sensitive data. A CSV file provides a clear, documented record that you can easily share and analyze. Secondly, Security. Knowing who has access helps you identify potential security risks. Are there users with excessive permissions? Are there old accounts that still have access? Exporting permissions is the first step to securing your environment. Troubleshooting is another big one. If users are reporting access issues, having a permission export can help you quickly pinpoint the problem. Site Migrations or Restructuring also benefit greatly. Before you move or reorganize your site, understanding the current permission structure is crucial for a smooth transition. So, as you can see, having this data readily available is super important for effective SharePoint management. It's not just about ticking boxes; it's about maintaining a secure and well-managed SharePoint environment. You want to be in control, right? And this is a fantastic way to get there.

Getting Started with PnP PowerShell

Before we can export anything, we need to make sure you've got PnP PowerShell set up and ready to go. If you haven't already, you'll need to install the module. You can usually do this via PowerShell itself. Just open up PowerShell as an administrator and run:

Install-Module PnP.PowerShell

This command will download and install the latest version of the PnP PowerShell module. It's pretty straightforward, but if you run into any issues, make sure your PowerShell version is up-to-date and you have the necessary execution policies set. Once it's installed, the next step is to connect to your SharePoint Online site. This is where the Connect-PnPOnline cmdlet comes in. You'll need the URL of your SharePoint site. Let's say your site is at http://yourtenant.sharepoint.com/sites/YourSite. You would use a command like this:

Connect-PnPOnline -Url "http://yourtenant.sharepoint.com/sites/YourSite" -UseWebLogin

The -UseWebLogin flag is super handy because it will pop up a modern authentication window, allowing you to log in with your SharePoint credentials securely. This is the recommended way to connect these days. Once you're connected, PnP PowerShell establishes a session with your site, allowing you to run all sorts of cmdlets against it. It's the gateway to managing your SharePoint environment programmatically. Think of it as your backstage pass to SharePoint! Make sure you're connected to the correct site, guys. Double-check that URL. A typo here can lead to a lot of confusion later on. After a successful connection, you'll see a confirmation, and you're all set to start querying your site's data. This initial setup is key, so take your time and ensure it's done right. It sets the stage for all the cool stuff we're about to do.

Understanding SharePoint Permissions

Before we jump into the code, it's important to have a basic understanding of how SharePoint permissions work, especially in the context of what we're exporting. SharePoint permissions are hierarchical. Permissions are granted at different levels: Site Collection, Site, List/Library, and even Item/Document level. When we talk about exporting permissions, we're usually referring to the Role Assignments. A Role Assignment links a User or Group (the Principal) to a Role (a set of Permissions, like 'Contribute' or 'Full Control'). These Role Assignments exist at various scopes within the site. Your Get-PnPWeb cmdlet, when used with the -Includes RoleAssignment parameter, fetches the role assignments directly associated with the site collection object itself. However, permissions can also be inherited from parent objects. For instance, a list might inherit permissions from the site, or an item might inherit from its list. When you're auditing, you often want to see the unique permissions set at each level, as well as the inherited ones. PnP PowerShell gives us the tools to traverse this hierarchy. We can get permissions for the site, then drill down into specific lists or libraries if needed. It's also important to distinguish between SharePoint Groups (like 'Members', 'Visitors', 'Owners') and Active Directory/Microsoft 365 Groups or individual users. PnP PowerShell can help you retrieve information about all of these. Understanding that a Role Assignment is the fundamental link between 'who' and 'what access they have' is key. This entity is what we'll be querying and exporting. So, when you see terms like 'Role Definition' and 'Member' in the output, remember they are components of these Role Assignments. The role definition specifies the type of access (e.g., Read, Edit, Full Control), and the member is the user or group receiving that access. Keep this structure in mind as we move forward, because it dictates how we'll retrieve and interpret the data.

Fetching Role Assignments

Alright, let's get to the heart of it! You've connected to your site, and now you want to grab those permission details. The cmdlet you'll be using is Get-PnPRoleAssignment. This cmdlet is your best friend for retrieving information about users and groups that have been granted access to SharePoint resources. When you run Get-PnPRoleAssignment, it returns a collection of objects, each representing a unique assignment of a principal (user or group) to a role definition (permission level) at a specific scope. The scope could be the site itself, a list, a library, or even down to an individual item, depending on how you scope your query.

To get the role assignments for the current site you are connected to, you can use:

Get-PnPRoleAssignment

This command will retrieve all role assignments at the site level. However, each role assignment object returned is quite rich. It contains properties like Member (the user or group), RoleDefinition (the permission level), and PrincipalType (whether it's a user, SharePoint group, etc.). Often, you'll want to see more details about the member, such as their login name or email address. For SharePoint groups, you might want to know the members within those groups. This is where things can get a little more involved, but PnP PowerShell makes it manageable.

If you've already retrieved the web object with role assignments like you mentioned ($web = Get-PnPWeb -Includes RoleAssignment), you can access them through $web.RoleAssignments. This is essentially equivalent to running Get-PnPRoleAssignment when you're scoped to the site. The key is to understand that Get-PnPRoleAssignment can be used at different scopes. For example, to get role assignments for a specific list, you would first get the list object and then use Get-PnPRoleAssignment -List $listObject. But for exporting site-level permissions, the basic Get-PnPRoleAssignment is often what you need.

It's also worth noting that you might encounter different types of principals: users, SharePoint groups, and security groups (like those from Microsoft 365/Azure AD). The PrincipalType property helps differentiate these. For a comprehensive audit, you'll want to capture all of these. So, the Get-PnPRoleAssignment cmdlet is your starting point. It fetches the raw data, and we'll then process this data to make it CSV-friendly.

Extracting Relevant Information

Okay, so Get-PnPRoleAssignment gives us a lot of information, but it's not all in a format that's immediately useful for a CSV. We need to extract the specific details we want and format them nicely. For each role assignment, we're typically interested in:

  1. The Principal (User/Group Name): Who is getting the permission?
  2. The Principal Type: Is it a user, a SharePoint group, or a security group?
  3. The Role Definition Title: What kind of access do they have (e.g., 'Read', 'Contribute', 'Full Control')?
  4. The Scope: Where is this permission applied? (For site-level exports, this might be consistent, but it's good to be aware).

Let's break down how to get this. When you run Get-PnPRoleAssignment, each object has a Member property and a RoleDefinition property. The Member property is an object itself, which has a Title (usually the display name) and a LoginName (the user's UPN or group name). The RoleDefinition object has a Name property, which is the permission level's name.

So, for each role assignment $ra in your retrieved collection, you'd typically access these properties like so:

  • Principal Name: $ra.Member.Title
  • Principal Login: $ra.Member.LoginName
  • Permission Level: $ra.RoleDefinition.Name

We also want to capture the PrincipalType, which is directly available as $ra.PrincipalType. Now, to make this CSV-ready, we'll use Select-Object to pick out these specific properties and give them clear header names. We can also create custom properties if needed. For example, to create a more detailed output object, you might do:

$permissions = Get-PnPRoleAssignment | Select-Object @{
    Label = "User/GroupName"
    Expression = {$_.Member.Title}
}, @{
    Label = "UserLogin"
    Expression = {$_.Member.LoginName}
}, @{
    Label = "PermissionLevel"
    Expression = {$_.RoleDefinition.Name}
}, @{
    Label = "PrincipalType"
    Expression = {$_.PrincipalType}
}

This creates a new collection of objects, each with the properties we defined. The @{Label = "..."; Expression = {...}} syntax is a powerful way in PowerShell to create calculated properties, which is perfect for renaming columns or transforming data. It makes your output much cleaner and easier to understand in the CSV file. Remember, you might have users who are members of multiple SharePoint groups. This script will list each direct role assignment. If you need to see the effective permissions of a user (considering group memberships), that's a more complex query involving recursive lookups, but for direct role assignments, this method works great. This extraction step is crucial for transforming raw data into a usable report.

Handling SharePoint Groups and Memberships

This is where things can get a bit trickier, guys, but it's super important for a complete picture. When Get-PnPRoleAssignment returns a principal that is a SharePoint Group (e.g., 'Site Members', 'Site Visitors'), the Member.Title will be the group name, and Member.LoginName might be something like c:0-.f|sprt|groupname. What we often really want to know is who is actually in those SharePoint groups. This requires an extra step: iterating through the groups and getting their members.

Here’s how you can extend the previous script to handle this. We'll process each role assignment. If it's a SharePoint group, we'll then fetch the members of that group and create separate entries in our output for each member, along with the role assigned to the group.

$sitePermissions = @()

$roleAssignments = Get-PnPRoleAssignment

foreach ($ra in $roleAssignments) {
    $principalName = $ra.Member.Title
    $principalLogin = $ra.Member.LoginName
    $permissionLevel = $ra.RoleDefinition.Name
    $principalType = $ra.PrincipalType

    if ($principalType -eq "SharePointGroup") {
        # Get members of the SharePoint group
        $group = Get-PnPRole -Identity $principalLogin -List $ra.Principal.ParentWeb.Lists['Site Assets'] # Placeholder, need correct scope
        # This part is tricky: need to get the actual SP group object correctly
        # A better approach is often to get the group by name and then its users
        try {
            $spGroup = Get-PnPGroup -Identity $principalName -ErrorAction Stop
            foreach ($member in $spGroup.Users) {
                $sitePermissions += [PSCustomObject]@{ 
                    UserOrGroupName = $member.Title 
                    UserLogin = $member.LoginName 
                    PermissionLevel = $permissionLevel 
                    PrincipalType = "SharePoint Group Member" 
                    GroupName = $principalName
                }
            }
        } catch {
            # Handle cases where the group might not be found or has no users
            $sitePermissions += [PSCustomObject]@{ 
                UserOrGroupName = $principalName # Fallback to group name
                UserLogin = $principalLogin # Fallback to group login
                PermissionLevel = $permissionLevel 
                PrincipalType = "SharePoint Group (No Members Listed)" 
                GroupName = "N/A" 
            }
        }

    } else {
        # It's a user or a security group, add directly
        $sitePermissions += [PSCustomObject]@{ 
            UserOrGroupName = $principalName 
            UserLogin = $principalLogin 
            PermissionLevel = $permissionLevel 
            PrincipalType = $principalType 
            GroupName = "N/A" 
        }
    }
}

# Now $sitePermissions contains the processed data
$sitePermissions | Export-Csv -Path "C:\temp\SitePermissions.csv" -NoTypeInformation

Important Note: The snippet for getting SharePoint group members (Get-PnPGroup) is the more straightforward and recommended way. The initial thought of using -List with Get-PnPRole might be confusing as Get-PnPRoleAssignment is more direct for the assignment itself. The Get-PnPGroup cmdlet is designed to retrieve SharePoint group objects, and then you can access the Users property of that group object. This loop ensures that if a user has access because they are in a SharePoint group, they will appear in the report with their individual name, the permission level granted to the group, and the name of the group they belong to.

This approach gives you a much more granular view. Instead of just seeing 'Site Members' have 'Contribute' access, you'll see each individual user who is a member of 'Site Members' and has 'Contribute' access. This is critical for detailed audits. For direct users or security groups, we simply add them as is. The GroupName field is added to indicate which group membership granted the permission, which is useful context. Remember to handle potential errors, like groups that might have been deleted or are empty. The try-catch block helps with that. This step truly elevates your permission report from basic to comprehensive!

Exporting to CSV

We've gathered the data, processed it, and now it's time to save it all into a nice, clean CSV file. This is where the Export-Csv cmdlet comes in, and it's super straightforward. Once you have your collection of custom objects (like the $sitePermissions array we built in the previous step), you just pipe it to Export-Csv.

Let's assume you've run the script and populated $sitePermissions with all the extracted and processed data. You'll want to specify a path for your CSV file. For example, you might want to save it to your C: emp folder:

$sitePermissions | Export-Csv -Path "C:\temp\SharePointPermissionsReport.csv" -NoTypeInformation

Let's break down those parameters:

  • -Path "C:\temp\SharePointPermissionsReport.csv": This tells Export-Csv exactly where to save the file and what to name it. You can change this path to wherever you prefer. Make sure the directory exists, or the command might fail.
  • -NoTypeInformation: This is a really important parameter, guys. Without it, Export-Csv includes an extra line at the very top of the CSV file that looks something like #TYPE System.Management.Automation.PSCustomObject. This line is usually not needed for data analysis in Excel or other spreadsheet software and can sometimes cause issues. Using -NoTypeInformation prevents this extra header line, ensuring your CSV is clean and contains only your data.

Once this command runs, you'll have a file named SharePointPermissionsReport.csv (or whatever you named it) in the specified location. You can then open this file in Microsoft Excel, Google Sheets, or any other spreadsheet application. You'll see your data neatly organized into columns with headers corresponding to the properties you selected (like 'User/GroupName', 'UserLogin', 'PermissionLevel', 'PrincipalType', 'GroupName').

This CSV is now your actionable report. You can sort it, filter it, analyze it for compliance, identify users with excessive privileges, or use it as a baseline for future audits. The power of having this data in a structured format like CSV cannot be overstated. It transforms complex SharePoint permission settings into easily digestible information. So, go ahead, export it, and take control of your site's security!

Script Example: Putting It All Together

Alright, let's consolidate everything we've discussed into a single, usable script. This script will connect to your SharePoint site, retrieve all role assignments, process them to include group memberships, and then export the results to a CSV file. Remember to replace `