Generate user report

Question

Hi All,

We are using Microsoft 365 E5 licensing. I need to identify all SharePoint Online sites where a specific user (e.g., user1(at)contoso.com) has any of the following roles:

Site Collection Administrator

Site Owner

Site Member

Member (via SharePoint group or Microsoft 365 group)

In short, I am looking for a way to retrieve all SharePoint Online sites where this user has Owner, Member, or Admin access.

I reviewed the following Microsoft documentation, but I’m not clear on how to use it to extract this information for a single user across all sites:

https://learn.microsoft.com/en-us/microsoft-365/enterprise/manage-sharepoint-users-and-groups-with-powershell?view=o365-worldwide

Could someone please guide me on the recommended approach (PowerShell / PnP / Graph) to achieve this? if possible please help me with the script to achieve this.

Answer 1

Dear @Glenn Maxwell,

Thank you for reaching out to the Microsoft Q&A forum. I am happy to assist you.

First, I would like to clarify that this is a user-to-user community forum. As an admin moderator, I can provide general guidance, basic knowledge, and troubleshooting steps based on the information you share. This forum primarily focuses on product usage, configuration guidance, and general best practices rather than delivering fully customized scripting solutions or tenant-specific automation code.

Since each Microsoft 365 tenant can have its own configuration, security policies, licensing setup, and permission architecture, it is difficult to provide a 100% accurate or fully tailored script for advanced reporting scenarios, such as generating a comprehensive report of all SharePoint Online sites where a specific user has roles (e.g., Site Collection Admin, Owner, Member, or access via Microsoft 365 group).

As a next step, I recommend posting your detailed requirement in communities where scripting and advanced automation experts are more active, such as:

• Microsoft Tech Community (SharePoint / Microsoft 365 Developer space)
• Stack Overflow

When posting, you may consider including additional details such as:

• Whether the sites are group-connected or classic sites
• Approximate number of sites in the tenant
• Whether Microsoft 365 E5 is assigned and Data Access Governance is enabled
• The specific PowerShell module or API being used (SharePoint Online module, PnP PowerShell, Microsoft Graph, etc.)

Providing these details will help experts better understand your environment and provide more technically accurate assistance.

If you require direct, tenant-specific support, you may also raise a support request ticket via the Microsoft 365 Admin Center. The Microsoft Support team can engage directly with you and, with your permission, access your tenant using internal diagnostic tools to provide more accurate analysis and recommendations.

The Global Admin can create a support ticket by sign in to the Microsoft 365 Admin Center > Support > Help & Support.  

For detailed instructions on how to get support, please refer to Get support - Microsoft 365 admin. 

Note: If you’re an end-user, please coordinate with your Office 365 Global Administrator to handle these steps.

I hope this information is helpful. If you need any further assistance, please feel free to reach out. I am here to help. Thank you very much for your understanding and cooperation.

Answer 2

Use the built-in Data access governance “site permissions for users” report or the corresponding SharePoint Online PowerShell cmdlet to get a tenant-wide view of where a specific user has access.

With Microsoft 365 E5, the recommended approach is:

1. Generate a user permission state report with SharePoint Online PowerShell
   • First, get the user principal name (UPN) if needed (for example, via Get-MgUser).
   • Then run the Data access governance permissions report for that user across SharePoint:

      Start-SPODataAccessGovernanceInsight \
        -ReportEntity PermissionsReport \
        -ReportType Snapshot \
        -Workload SharePoint \
        -Name "UserSharePointReport" \
        -UserEmailList "******@contoso.com"
   

   Notes from the documentation:
   • -UserEmailList expects user principal names.
   • Up to 100 users can be included per request.
   • To target OneDrive instead of SharePoint, use -Workload OneDriveForBusiness.
   This report lists all SharePoint sites accessible to the specified user. It covers access regardless of whether it is via:
   • Site Collection Administrator
   • Site Owner
   • Site Member
   • Membership via SharePoint group or Microsoft 365 group
2. Use the Data access governance UI to view and download the report After the snapshot report is generated:
   1. Go to the Data access governance landing page in the SharePoint admin experience.
   2. Under Site permissions for users, select View reports.
   3. Select Create report if not already done, or open the report created by the PowerShell command.
   4. Confirm the Scope is SharePoint and that the user ******@contoso.com is in the user list.
   5. Once the report status shows as ready, select the Report name to view it.
   6. The left side lists users; select ******@contoso.com. The right side shows all sites that user can access and whether access is to the entire site or specific items.
   7. Use the download option to export the full report if needed.
   Important constraints from the documentation:
   • The organization-wide site permissions report must have been generated at least once before using the user-level report.
   • Reports capture data up to 48 hours before generation.
   • Maximum of 5 reports at a time.
   • Reports can be rerun every 30 days.
3. Classic SharePoint Online PowerShell enumeration (if needed for custom reporting) If a fully custom script is required instead of the built-in Data access governance report, SharePoint Online PowerShell can enumerate users per site and then filter for the target user. For example, to dump all users per site:

      Get-SPOSite | ForEach { 
          Get-SPOUser -Site $_.Url 
      } | Format-Table -Wrap -AutoSize | Out-File C:\UsersReport.txt -Force -Width 360 -Append
   

   This produces a tenant-wide user listing per site. The output can then be filtered (for example, in PowerShell or after export) for ******@contoso.com and for the relevant roles (Owner/Member/Admin). The documentation example shows how to generate such a report; additional filtering logic can be added on top of it.

For the stated requirement (all sites where a specific user has Owner/Member/Admin access), the Data access governance “site permissions for users” snapshot report is the most direct and supported method, and the Start-SPODataAccessGovernanceInsight cmdlet is the recommended PowerShell entry point.

---

References:

• Manage Data access governance reports using SharePoint Online PowerShell
• Discover sites accessible by a given user with the snapshot report
• Manage SharePoint users and groups with PowerShell
• Use PowerShell to create reports for Microsoft 365
• Why you need to use PowerShell for Microsoft 365
• Export user data from Project Online