How to get any site collection users with their roles using PnP PowerShell?

Published 04-12-2021 12:48 AM 1,117 Views
Senior Member

Introduction

 

Sometimes we have a requirement to get reports of users and their roles on the SharePoint site and we have to export to CSV/Excel format. To achieve this solution we will use PnP Powershell

 

Let's see step-by-step implementation:

 

 

Implementation

 

  • Open Windows Powershell ISE
  • Create a new file and write a script

 

Now we will see all the steps which we required to achieve the solution:

 

1.  We will read the site URL from the user

2.  Then we will connect to the O365 admin site and then we will connect to the site which the user has entered

3. Create a function to get user and their roles and store it in an array

4. And we will export the array to CSV format

 

So in the end, our script will be like this,

 

 

 

$basePath = "E:\Chandani\Blogs\UserRolesPS\"
$dateTime = "{0:MM_dd_yy}_{0:HH_mm_ss}" -f (Get-Date)
$csvPath = $basePath + "\userdetails" + $dateTime + ".csv"
$adminSiteURL = "https://*****-admin.sharepoint.com/" #O365 admin site URL
$username = #Email ID
$password = "********"
$secureStringPwd = $password | ConvertTo-SecureString -AsPlainText -Force 
$Creds = New-Object System.Management.Automation.PSCredential -ArgumentList $username, $secureStringPwd
$global:userRoles = @()


Function Login() {
    [cmdletbinding()]
    param([parameter(Mandatory = $true, ValueFromPipeline = $true)] $Creds)
 
    #connect to the O365 admin site
    Write-Host "Connecting to Tenant Admin Site '$($adminSiteURL)'" -f Yellow
  
    Connect-PnPOnline -Url $adminSiteURL -Credentials $Creds
    Write-Host "Connection Successfull" -f Yellow 
   
}
Function StartProcessing {
    Login($Creds);
    GetUserRoles
}

Function GetUserRoles {
    try {
        $siteURL = Read-Host "Please enter site collcetion URL"
        Write-Host "Connecting to Site '$($siteURL)'" -f Yellow          
     
        Connect-PnPOnline -Url $siteURL -Credentials $Creds
    
        $web = Get-PnPWeb -Includes RoleAssignments
    
        foreach ($roles in $web.RoleAssignments) {
            $member = $roles.Member
            $loginName = get-pnpproperty -ClientObject $member -Property LoginName
            $title = get-pnpproperty -ClientObject $member -Property Title
            $rolebindings = get-pnpproperty -ClientObject $roles -Property RoleDefinitionBindings
            $roleName = $($rolebindings.Name)            
    
            $global:userRoles += New-Object PSObject -Property ([ordered]@{                   
                    UserName  = $title
                    LoginName = $loginName
                    Roles     = $roleName
                })            
        }       
    }
    catch {
        Write-Host -f Red "Error in connecting to Site '$($TenantSite)'"     
    } 
    Write-Host "Exporting to CSV" -ForegroundColor Yellow      
    $global:userRoles | Export-CSV $csvPath -NoTypeInformation
    Write-Host "Export to CSV successfully!" -ForegroundColor Yellow
}

StartProcessing

 

 

 

 

Output

 

UserRolesOP.png

 

Summary

 

So in this article, we have seen how to get users and their roles from any SharePoint site collection and convert it in CSV format.

 

Hope this helps! If it is helpful to you then share it with others.

 

Sharing is caring!

1 Comment
Senior Member

this is cool. I wonder if similar thing could be done using m365 CLI
I don't see any similar sample script in cli-microsoft365/docs/docs/sample-scripts/spo at main · pnp/cli-microsoft365 (github.com)
Do you think I could use this blog post as reference and use it opening a new issue suggesting to create a similar sample using CLI? 

%3CLINGO-SUB%20id%3D%22lingo-sub-2267307%22%20slang%3D%22en-US%22%3EHow%20to%20get%20any%20site%20collection%20users%20with%20their%20roles%20using%20PnP%20PowerShell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2267307%22%20slang%3D%22en-US%22%3E%3CH2%20id%3D%22toc-hId--497502347%22%20id%3D%22toc-hId--494580999%22%20id%3D%22toc-hId--494523245%22%20id%3D%22toc-hId--494580999%22%3EIntroduction%3C%2FH2%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESometimes%20we%20have%20a%20requirement%20to%20get%20reports%20of%20users%20and%20their%20roles%20on%20the%20SharePoint%20site%20and%20we%20have%20to%20export%20to%20CSV%2FExcel%20format.%20To%20achieve%20this%20solution%20we%20will%20use%20%3CSTRONG%3EPnP%20Powershell%3C%2FSTRONG%3E.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20see%20step-by-step%20implementation%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-182556023%22%20id%3D%22toc-hId-1992931834%22%20id%3D%22toc-hId-1992989588%22%20id%3D%22toc-hId-1992931834%22%3EImplementation%3C%2FH2%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EOpen%20%3CSTRONG%3EWindows%20Powershell%20ISE%3C%2FSTRONG%3E%3C%2FLI%3E%0A%3CLI%3ECreate%20a%20new%20file%20and%20write%20a%20script%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3ENow%20we%20will%20see%20all%20the%20steps%20which%20we%20required%20to%20achieve%20the%20solution%3A%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-60px%22%3E1.%26nbsp%3B%20We%20will%20read%20the%20site%20URL%20from%20the%20user%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-60px%22%3E2.%26nbsp%3B%20Then%20we%20will%20connect%20to%20the%20O365%20admin%20site%20and%20then%20we%20will%20connect%20to%20the%20site%20which%20the%20user%20has%20entered%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-60px%22%3E3.%20Create%20a%20function%20to%20get%20user%20and%20their%20roles%20and%20store%20it%20in%20an%20array%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-60px%22%3E4.%20And%20we%20will%20export%20the%20array%20to%20CSV%20format%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-60px%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3ESo%20in%20the%20end%2C%20our%20script%20will%20be%20like%20this%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powershell%22%3E%3CCODE%3E%24basePath%20%3D%20%22E%3A%5CChandani%5CBlogs%5CUserRolesPS%5C%22%0A%24dateTime%20%3D%20%22%7B0%3AMM_dd_yy%7D_%7B0%3AHH_mm_ss%7D%22%20-f%20(Get-Date)%0A%24csvPath%20%3D%20%24basePath%20%2B%20%22%5Cuserdetails%22%20%2B%20%24dateTime%20%2B%20%22.csv%22%0A%24adminSiteURL%20%3D%20%22https%3A%2F%2F*****-admin.sharepoint.com%2F%22%20%23O365%20admin%20site%20URL%0A%24username%20%3D%20%23Email%20ID%0A%24password%20%3D%20%22********%22%0A%24secureStringPwd%20%3D%20%24password%20%7C%20ConvertTo-SecureString%20-AsPlainText%20-Force%20%0A%24Creds%20%3D%20New-Object%20System.Management.Automation.PSCredential%20-ArgumentList%20%24username%2C%20%24secureStringPwd%0A%24global%3AuserRoles%20%3D%20%40()%0A%0A%0AFunction%20Login()%20%7B%0A%20%20%20%20%5Bcmdletbinding()%5D%0A%20%20%20%20param(%5Bparameter(Mandatory%20%3D%20%24true%2C%20ValueFromPipeline%20%3D%20%24true)%5D%20%24Creds)%0A%20%0A%20%20%20%20%23connect%20to%20the%20O365%20admin%20site%0A%20%20%20%20Write-Host%20%22Connecting%20to%20Tenant%20Admin%20Site%20'%24(%24adminSiteURL)'%22%20-f%20Yellow%0A%20%20%0A%20%20%20%20Connect-PnPOnline%20-Url%20%24adminSiteURL%20-Credentials%20%24Creds%0A%20%20%20%20Write-Host%20%22Connection%20Successfull%22%20-f%20Yellow%20%0A%20%20%20%0A%7D%0AFunction%20StartProcessing%20%7B%0A%20%20%20%20Login(%24Creds)%3B%0A%20%20%20%20GetUserRoles%0A%7D%0A%0AFunction%20GetUserRoles%20%7B%0A%20%20%20%20try%20%7B%0A%20%20%20%20%20%20%20%20%24siteURL%20%3D%20Read-Host%20%22Please%20enter%20site%20collcetion%20URL%22%0A%20%20%20%20%20%20%20%20Write-Host%20%22Connecting%20to%20Site%20'%24(%24siteURL)'%22%20-f%20Yellow%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%0A%20%20%20%20%20%20%20%20Connect-PnPOnline%20-Url%20%24siteURL%20-Credentials%20%24Creds%0A%20%20%20%20%0A%20%20%20%20%20%20%20%20%24web%20%3D%20Get-PnPWeb%20-Includes%20RoleAssignments%0A%20%20%20%20%0A%20%20%20%20%20%20%20%20foreach%20(%24roles%20in%20%24web.RoleAssignments)%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%24member%20%3D%20%24roles.Member%0A%20%20%20%20%20%20%20%20%20%20%20%20%24loginName%20%3D%20get-pnpproperty%20-ClientObject%20%24member%20-Property%20LoginName%0A%20%20%20%20%20%20%20%20%20%20%20%20%24title%20%3D%20get-pnpproperty%20-ClientObject%20%24member%20-Property%20Title%0A%20%20%20%20%20%20%20%20%20%20%20%20%24rolebindings%20%3D%20get-pnpproperty%20-ClientObject%20%24roles%20-Property%20RoleDefinitionBindings%0A%20%20%20%20%20%20%20%20%20%20%20%20%24roleName%20%3D%20%24(%24rolebindings.Name)%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20%24global%3AuserRoles%20%2B%3D%20New-Object%20PSObject%20-Property%20(%5Bordered%5D%40%7B%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20UserName%20%20%3D%20%24title%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20LoginName%20%3D%20%24loginName%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Roles%20%20%20%20%20%3D%20%24roleName%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7D)%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20%7D%20%20%20%20%20%20%20%0A%20%20%20%20%7D%0A%20%20%20%20catch%20%7B%0A%20%20%20%20%20%20%20%20Write-Host%20-f%20Red%20%22Error%20in%20connecting%20to%20Site%20'%24(%24TenantSite)'%22%20%20%20%20%20%0A%20%20%20%20%7D%20%0A%20%20%20%20Write-Host%20%22Exporting%20to%20CSV%22%20-ForegroundColor%20Yellow%20%20%20%20%20%20%0A%20%20%20%20%24global%3AuserRoles%20%7C%20Export-CSV%20%24csvPath%20-NoTypeInformation%0A%20%20%20%20Write-Host%20%22Export%20to%20CSV%20successfully!%22%20-ForegroundColor%20Yellow%0A%7D%0A%0AStartProcessing%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-185477371%22%20id%3D%22toc-hId-185535125%22%20id%3D%22toc-hId-185477371%22%3EOutput%3C%2FH2%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22UserRolesOP.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F271876iD40126E615957C85%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22UserRolesOP.png%22%20alt%3D%22UserRolesOP.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-862614393%22%20id%3D%22toc-hId--1621977092%22%20id%3D%22toc-hId--1621919338%22%20id%3D%22toc-hId--1621977092%22%3ESummary%3C%2FH2%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%20in%20this%20article%2C%20we%20have%20seen%20how%20to%20get%20users%20and%20their%20roles%20from%20any%20SharePoint%20site%20collection%20and%20convert%20it%20in%20CSV%20format.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHope%20this%20helps!%20If%20it%20is%20helpful%20to%20you%20then%20share%20it%20with%20others.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESharing%20is%20caring!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-2267307%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20this%20article%2C%20we%20will%20see%20how%20to%20get%20users%20and%20their%20roles%20from%20any%20site%20collection%20and%20export%20it%20to%20CSV%20using%20PnP%20PowerShell.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2267307%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EHow%20to%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPnP%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPnP%20PowerShell%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2276908%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20get%20any%20site%20collection%20users%20with%20their%20roles%20using%20PnP%20PowerShell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2276908%22%20slang%3D%22en-US%22%3E%3CP%3Ethis%20is%20cool.%20I%20wonder%20if%20similar%20thing%20could%20be%20done%20using%20%3CA%20href%3D%22https%3A%2F%2Fpnp.github.io%2Fcli-microsoft365%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Em365%20CLI%3C%2FA%3E%3CBR%20%2F%3EI%20don't%20see%20any%20similar%20sample%20script%20in%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2Fpnp%2Fcli-microsoft365%2Ftree%2Fmain%2Fdocs%2Fdocs%2Fsample-scripts%2Fspo%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ecli-microsoft365%2Fdocs%2Fdocs%2Fsample-scripts%2Fspo%20at%20main%20%C2%B7%20pnp%2Fcli-microsoft365%20(github.com)%3C%2FA%3E%3CBR%20%2F%3EDo%20you%20think%20I%20could%20use%20this%20blog%20post%20as%20reference%20and%20use%20it%20opening%20a%20new%20issue%20suggesting%20to%20create%20a%20similar%20sample%20using%20CLI%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Co-Authors
Version history
Last update:
‎Apr 12 2021 12:48 AM
Updated by: