WIDBA - The busiest DBA in Wisconsin, its all I do and I do it well

Saturday, March 5, 2011

Putting on your SOX with Get-Acl

It is that time of year, when a few college graduates hired by the auditing company show up at the office.  Their mission is to make a name for themselves find folks who are not following our Sarbanes-Oxley policies.  The general idea is not all that bad, but in practice much of the audit is hokum.  They request permissions on one folder, but not the folder beneath it with the critical data.  They ask about who has access to change a job, but not who can edit financial data.  Anyway, I got my detailed audit request this past week and thus I utilized some powershell scripts that I created last year.  Last year I fought the screenshot vs powershell battle and won.  I would provided the report and the script I used so they could independently verify if they so chose.  This gives us consistency each year despite the auditors being changed out quicker than kegs at the tavern during a Packers game.

This script creates a HTML report of the Access Control Lists (ACL) for specific folders on the database server.  The report is far from pretty but creates something viewable with a little formatting.  It utilizes the Get-Acl cmdlet with some filters to return the users. 

$OutputFile = "C:\SOX2011\SQL_ACL_SOX2011.htm"
$headerString = "<br/>-------------------------------------------------------------------------<br/>"
$title = "<h1>SQL SOX2011</h1>"
$title | Out-File $OutputFile

# Build array of the paths to be audited for each server
$arr =  @("\\server\c$\Program Files\Microsoft SQL Server\")`
        ,("\\server\c$\Program Files\Microsoft SQL Server\MSSQL10.Brewers")`
        ,("\\server2\c$\Program Files\Microsoft SQL Server\")`
        ,("\\server2\c$\Program Files\Microsoft SQL Server\MSSQL10.Packers")

# Loop for each Path
foreach($BasePath in $arr)
    $subHeader = "<h2>" + $BasePath + "</h2>"
    $headerString | out-File  $OutputFile -append

    Get-Acl -path $BasePath | select -expand access |
        Where-Object -FilterScript { $_.FileSystemRights -notlike "[-,0-9][0-9][0-9]*"}   |
        Select-Object IdentityReference,AccessControlType,IsInherited,FileSystemRights  |
        ConvertTo-HTML -body $subHeader | Out-File $OutputFile -append      

This mini-series will have four parts on the scripts used to get through a SOX audit each year.


  1. Nice script. But, how do I tell it to go through all the sub directories also?

    Looking forward to your 4 other scripts

  2. @Stowy - Hopefully this will help in principle.

    Simply use Get-ChildItem via the pipeline.

    $BasePath = "c:\Program Files\Microsoft SQL Server\"

    Get-ChildItem $BasePath | Get-Acl | select-object path,owner,accesstostring,group | Out-GridView

    Hope its useful!