AzureFeeds

To help illustrate the need for this post, let’s start with a scenario:

Managing permissions for shared mailboxes can be a tedious task, especially when dealing with a large number of object for bulk operations. I have seen situations where the data needed for permission assignments was in an Excel file, which required extensive modifications to make it usable for simple import using PowerShell. But what if converting the data into a required .CSV format requires more time and effort than the actual task of assigning permissions?

Information in this blog post can help you in similar situations too, not just during permission assignments.

Here is a snippet of the sample Excel file that might contain data that we need to import using PowerShell:

The Problem

PowerShell provides the import-csv function, which is widely used for bulk operations and can be applied in this context as well. The challenge lies in preparing the source data and converting it into an acceptable CSV (comma-separated values) format. For instance, each user listed under the Full Access and Send Access columns in the above example must be processed separately by PowerShell. For that we can use Excel functions such as ‘Text to Column’, ‘Transpose Paste’, and ‘Flash Fill’ to prepare the CSV file. This process is both time-consuming and labor-intensive, and it carries the risk of errors due to manual copy-pasting.

The Solution

This solution involves a PowerShell script that loads the same Excel file containing the necessary user information and permissions. The script will iterate over each row of the Excel file, extract the relevant data, and assign the appropriate permissions to each user.

Prerequisites

Before running the script, ensure you have the following:

• Microsoft Excel installed on your machine.


• The Exchange Online PowerShell module installed.


• Administrative privileges to run the script and modify mailbox permissions.

The Script

Here is the PowerShell script that accomplishes this task:

# Load the Excel file
$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.Open(“C:migEMEA-3IT_Pilotsnb_access.xlsx”)
$sheet = $workbook.Sheets.Item(1)
# Get the number of rows
$rowCount = $sheet.UsedRange.Rows.Count
# Iterate over each row in the Excel file
for ($row = 2; $row -le $rowCount; $row++) {
$sharedMailbox = $sheet.Cells.Item($row, 1).Text
$saUsers = $sheet.Cells.Item($row, 2).Text -split “[,;]”
$faUsers = $sheet.Cells.Item($row, 3).Text -split “[,;]”
# Add Send As permission for SA users
foreach ($user in $saUsers) {
Add-RecipientPermission -Identity $sharedMailbox -Trustee $user.Trim() -AccessRights SendAs -Confirm:$false
}
# Add Full Access permission for FA users
foreach ($user in $faUsers) {
Add-MailboxPermission -Identity $sharedMailbox -User $user.Trim() -AccessRights FullAccess -InheritanceType All -Confirm:$false
}
}
# Close the Excel file
$workbook.Close($false)
$excel.Quit()

Explanation

The script performs the following steps:

• Loads the Excel file using the ComObject ‘Excel.Application’.


• Opens the specific workbook and selects the first sheet.


• Counts the number of used rows in the sheet.


• Iterates over each row to read the shared mailbox and the users to whom permissions need to be assigned.


• Splits the user lists into individual users based on comma or semicolon delimiters.


• Adds “Send As” permissions to the specified users.


• Adds “Full Access” permissions to the specified users.


• Closes the Excel file and quits the Excel application.

This script ensures that permissions are assigned consistently and efficiently, reducing the chances of errors and saving administrative time.

Pro Tip:

If the data is separated using any other delimiters, ensure it is enclosed within square brackets in lines 10 and 11 of the script.

Conclusion

This post showed how the example scenario of assignment of permissions for shared mailboxes using PowerShell and Excel source file (vs. a specifically formatted .CSV file) can improve efficiency. Using this script as a sample, administrators can hopefully speed up some of their other import tasks and bulk operations.

Feel free to customize the script according to your specific requirements!

Thanks for reading!

Abhijeet Kowale and Indraneel Roy