Export Permissions to a Report

Many times a DBA is asked to provide the list of permissions, for auditing purposes, or simply to review the current permissions. Sometimes the request is for server-wide permissions, sometimes the scope is limited to a database, sometimes to a Login, or combinations or “all of the above”.

Of course we can write our own custom code, as I did with the Report Server-level and Database-level permissions for a User. script.

One alternative is to use the brilliant dbatools and ImportExcel PowerShell modules.

The below PowerShell script uses these two modules to retrieve all server and database permissions, then export the results to separate tabs in an Excel file.

Note that Microsoft Excel is not required to generate the output, however it (or a compatible application) is required to open the file/s generated.

Import-Module dbatools,ImportExcel

# report output parameters
[string] $ReportFileName = "PermissionsReport"
[string] $ExportFolder = "C:\Users\Public\Documents"
[string] $ExportFileName = "$(Get-Date -Format 'yyyyMMdd_HHmmss')"
[string] $ExportFilePath = "$ExportFolder\$($ReportFileName)_$($ExportFileName).xlsx"

# list of instances to report on
$Instances = @(
    "localhost,14331"
    ,"localhost,14332"
    ,"localhost,14333"
)

Write-Host $("Export started at {0}" -f $(Get-Date -Format "yyyy=MM-dd HH:mm:ss"))
Write-Host "Output file: $ExportFilePath"

# remove any file with the same name
if (Test-Path -Path $ExportFilePath -PathType Leaf) { Remove-Item -Path $ExportFilePath -Force }

# export data
foreach ($Instance in $Instances) {
    Write-Host "Processing $Instance"
    Get-DbaUserPermission -SqlInstance $Instance | Export-Excel -Path $ExportFilePath -AutoSize -FreezeTopRow -BoldTopRow -WorksheetName $($Instance.replace("\", "$"))
}

Write-Host $("Export completed at {0}" -f $(Get-Date -Format "yyyy=MM-dd HH:mm:ss"))

The latest version of the above code can be found here: Export-Permissions-to-a-Report.ps1

More info: