How to Report Permissions in SQL Server Using PowerShell

powershellsql server

I want to write a report that gets all the permissions for every Login in our SQL server farm. For now I can get the logins but get stuck there trying to connect the logins to roles. Haven't even found a way to get the roles yet. Are there PowerShell functions available for this or do I need to write sql statements and parse the results with PowerShell? Alternatively, Am i making it too complicated and should get to the information from a different angle?

#Get all the logins for every server
#Filters out all "NT Service" and "NT Authority" accounts as well as MSPolicy Accounts
$SQLLogins = Get-SqlLogin -ServerInstance $DefaultServerList | Where {($_.name -notmatch "^NT Service\\.*") -and ($_.name -notmatch "^NT AUTHORITY\\.*") -and ($_.name -notmatch "##.*##")}

#For every one, determine the permissions
ForEach ($Login in $SQLLogins) {
    Write-Host $Login.name "    " $Login.Parent.Name
}

Best Answer

As @user1716729 noted in their answer, dbatools can do this quite well. In fact, I have a script that produces exactly such a report. I can't share the whole thing as I wrote it expressly for my employer, but I do the following:

This takes less than 10 seconds to execute against my estate of about 16 instances and my security folks love the results.

Related Question