Export SharePoint Users and Groups to Excel using PowerShell

Requirement: Export SharePoint Users and Groups to Excel for analyzing SharePoint Groups and Users along with their Account Name, E-mails!We can export SharePoint User Group to excel using PowerShell. Here is how:

PowerShell Script to Export Users & Groups:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")   #Using Get-SPSite in MOSS 2007 function global:Get-SPSite($url)  {     return new-Object Microsoft.SharePoint.SPSite($url)  }   function global:Get-SPWeb($url) {   $site= New-Object Microsoft.SharePoint.SPSite($url)         if($site -ne $null)             {                $web=$site.OpenWeb();                   }     return $web }   $URL="http://sharepoint.crescent.com/sites/csaportal/"         $site = Get-SPSite $URL           #Write the Header to "Tab Separated Text File"         "Site Name`t  URL `t Group Name `t User Account `t User Name `t E-Mail" | out-file "d:\UsersandGroupsRpt.txt"                #Iterate through all Webs       foreach ($web in $site.AllWebs)       {         #Write the Header to "Tab Separated Text File"         "$($web.title) `t $($web.URL) `t  `t  `t `t " | out-file "d:\UsersandGroupsRpt.txt" -append          #Get all Groups and Iterate through             foreach ($group in $Web.groups)          {                 "`t  `t $($Group.Name) `t   `t `t " | out-file "d:\UsersandGroupsRpt.txt" -append                 #Iterate through Each User in the group                        foreach ($user in $group.users)                         {                            #Exclude Built-in User Accounts                     if(($User.LoginName.ToLower() -ne "nt authority\authenticated users") -and ($User.LoginName.ToLower() -ne "sharepoint\system") -and ($User.LoginName.ToLower() -ne "nt authority\local service"))                     {                                 "`t  `t  `t  $($user.LoginName)  `t  $($user.name) `t  $($user.Email)" | out-file "d:\UsersandGroupsRpt.txt" -append                              }                         }          }        }     write-host "Report Generated at d:\UsersandGroupsRpt.txt"

This script will Export SharePoint user group to excel. Here is the report output:

  
PowerShell script to Get All Groups and Members of Each group:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")   #Using Get-SPSite in MOSS 2007 function global:Get-SPSite($url)  {     return new-Object Microsoft.SharePoint.SPSite($url)  }   function global:Get-SPWeb($url) {   $site= New-Object Microsoft.SharePoint.SPSite($url)         if($site -ne $null)             {                $web=$site.OpenWeb();                      }     return $web }   $URL="https://sharepoint.crescent/sites/helpdesk/us"         $site = Get-SPSite $URL             if (Get-SPWeb($url).HasUniqueRoleAssignments -eq $true)      {         $Web=Get-SPWeb($url)      }      else      {         $web= $site.RootWeb      }        #Get all Groups and Iterate through         foreach ($group in $Web.sitegroups)      {         write-host " Group Name: "$group.name "`n---------------------------`n"             #Iterate through Each User in the group                    foreach ($user in $group.users)                     {                         write-host $user.name  "`t" $user.LoginName  "`t"  $user.Email  | FT                     }      write-host "=================================="  #Group Separator      }

How to Get members of a particular group in SharePoint 2007 using PowerShell:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")   #Using Get-SPSite in MOSS 2007   function global:Get-SPSite($url)  {     return new-Object Microsoft.SharePoint.SPSite($url)  }   Function global:Get-SPWeb($url) {   $site= New-Object Microsoft.SharePoint.SPSite($url)         if($site -ne $null)             {                $web=$site.OpenWeb()              }     return $web }   $URL="http://sharepoint.crescent.com/sites/operations/"        $site = Get-SPSite $URL      $web= $site.OpenWeb()         #Get the Group by its name      $Group = $Web.sitegroups | Where-Object {$_.Name -eq "CSA Test All Entity Users"}                 #Iterate through Each User in the group                    foreach ($user in $group.users)                     {                         write-host $user.name  "`t" $user.LoginName "`t"  $user.Email                       }

Its also possible to read user properties from User Profile.

PowerShell script to read user Profile Properties: 

?

1 2 3 4 5 6

#Get Profile Properties from User Profile $ServerContext=[Microsoft.Office.Server.ServerContext]::GetContext($site)  $UPM = New-Object Microsoft.Office.Server.UserProfiles.UserProfileManager($ServerContext)  $UserProfile=$upm.GetUserProfile($user.LoginName )  $Title=$UserProfile["Title"].Value  $Department=$UserProfile["Department"].Value

Related Posts:

• SharePoint Users and Groups Security Report Based on Permission Levels
• SharePoint Users and Groups Permission Analysis Report

Reference:
http://www.sharepointdiary.com/2013/07/export-sharepoint-users-and-groups-to-excel-using-powershell.html