Getting NTFS permissions of all shared folders on the local machine

Question

How can I improve the speed of the script?

$Excel = New-Object -Com Excel.Application
$Excel.visible = $True
$Excel = $Excel.Workbooks.Add()

$wSheet = $Excel.Worksheets.Item(1)
$wSheet.Cells.item(1, 1) = "Folder Path:"
$wSheet.Cells.Item(1, 2) = "Users/Groups:"
$wSheet.Cells.Item(1, 3) = "Permissions:"
$wSheet.Cells.Item(1, 4) = "Permissions Inherited:"

$WorkBook = $wSheet.UsedRange
$WorkBook.Interior.ColorIndex = 8
$WorkBook.Font.ColorIndex = 11
$WorkBook.Font.Bold = $True

 ####Change the path to the folder or share you want NTFS perms on####
 $dirToAudit = Get-ChildItem -Path "c:\inetpub" -recurse | Where { $_.psIsContainer -eq $true }

 $intRow = 1
 foreach ($dir in $dirToAudit)
 {
     $colACL = Get-Acl -Path $dir.FullName

foreach ($acl in $colACL)
 {
    $intRow++
    $wSheet.Cells.Item($intRow, 1) = $dir.FullName

    foreach ($accessRight in $acl.Access)
    {
        $wSheet.Cells.Item($intRow, 2) = "$($AccessRight.IdentityReference)"
        $wSheet.Cells.Item($intRow, 3) = "$($AccessRight.FileSystemRights)"
        $wSheet.Cells.Item($intRow, 4) = $acl.AreAccessRulesProtected
        $intRow++
    }
}

}
 $WorkBook.EntireColumn.AutoFit()

Answer 1

The only way to improve the speed of the script is to drop the usage of Excel as a COM-object altogether. It is considered bad practice. And it is not possible on servers where Excel is not installed.

It's better to use a library like NPOI (.NET port from Apache POI (java)).

I used this (old) example as a base and adapted it to roughly match your code.

[Reflection.Assembly]::LoadFrom("C:\Users\IEUser\Desktop\Release\Net40\NPOI.dll")
[Reflection.Assembly]::LoadFrom("C:\Users\IEUser\Desktop\Release\Net40\NPOI.OOXML.dll")
[Reflection.Assembly]::LoadFrom("C:\Users\IEUser\Desktop\Release\Net40\NPOI.OpenXml4Net.dll")
[Reflection.Assembly]::LoadFrom("C:\Users\IEUser\Desktop\Release\Net40\NPOI.OpenXml4Net.dll")
[Reflection.Assembly]::LoadFrom("C:\Users\IEUser\Desktop\Release\Net40\NPOI.OpenXmlFormats.dll")
[Reflection.Assembly]::LoadFrom("C:\Users\IEUser\Desktop\Release\Net40\ICSharpCode.SharpZipLib.dll")

$wb = New-Object NPOI.XSSF.UserModel.XSSFWorkbook;
$ws = $wb.CreateSheet("output");
$ws.CreateRow(0)| out-null;

$dirToAudit = Get-ChildItem -Path "C:\Users\IEUser" -recurse | Where { $_.psIsContainer -eq $true }

$intRow = 1

foreach ($dir in $dirToAudit)
{
    $colACL = Get-Acl -Path $dir.FullName

    foreach ($acl in $colACL)
    {
        $fileNameRow = $ws.CreateRow($intRow)
        $fileNameRow.CreateCell(1).SetCellValue($dir.FullName)
        $intRow++

        foreach ($accessRight in $acl.Access)
        {
            $values = $ws.CreateRow($intRow)
            $values.CreateCell(2).SetCellValue($($AccessRight.IdentityReference).ToString())
            $values.CreateCell(3).SetCellValue($($AccessRight.FileSystemRights).ToString())
            $values.CreateCell(4).SetCellValue($($acl.AreAccessRulesProtected).ToString())
            $intRow++
        }
    }

}
$fs = new-object System.IO.FileStream("C:\Users\IEUser\Desktop\test.xlsx",[System.IO.FileMode]'Create',[System.IO.FileAccess]'Write')
$wb.Write($fs);
$fs.Close()

There is also another library for this kind of stuff: EPPLus

And someone else made some nice powershell functions for it.

Answer 2

I don't see any real need for Excel here at all actually. Any reason you could not just use a CSV that can be opened in Excel? Export-CSV and using more of the pipeline would most certainly be faster here.

$path = 'rootpath'
$exportPath = 'Path to export'
Get-ChildItem -Path $path -Recurse | Where-Object {$_.psIsContainer} | ForEach-Object{
    # Process each folder
    $singleFolder = $_
    $aclSet = $singleFolder | Get-Acl
    $aclset.Access | ForEach-Object{
        # Build a custom object, using a hashtable, for each access control set
        $props = @{
            "Folder Path" = $singleFolder.FullName
            "Users/Groups" = $_.IdentityReference
            Permissions = $_.FileSystemRights
            "Permissions Inherited" = $aclset.AreAccessRulesProtected
        }

        # Create and send the object down the pipeline
        New-Object -TypeName psobject -Property $props
    }

# Ensure that the properties appear in the desired order. Issue with PowerShell 2.0
} | Select-Object "Folder Path", "Users/Groups", Permissions, "Permissions Inherited" |
    Export-CSV -NoTypeInformation -Path $exportPath

Other thoughts

• psIsContainer is itself a boolean so you don't need to test it being true. PowerShell will do that all on its own.
• There is no error handling in your or my script snippet. If you don't have the right to read the folders, export the data and etc. the script would fail.