Report of permissions in SQL server

Question

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
}

Answer 1

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:

• Execute the following functions and capture the results to individual variables:
  • Get-DbaLogin
  • Get-DbaDbRole
  • Get-DbaDbRoleMember
  • Get-DbaServerRole
  • Get-DbaServerRoleMember
• For each of those sets of results, pipe through Select-Object to get the fields I need (typically InstanceName, Login/User name, Database, Role, etc. Then pipe to Export-Excel (from the Import-Excel module with each collection going to a different sheet in the workbook.

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

Answer 2

I am not 100% sure but dbatools.io might have one.

Answer 3

Are there PowerShell functions available for this or do I need to write sql statements and parse the results with PowerShell?

In general you switch to using SQL through Invoke-Sqlcmd whenever it's not obvious how to do something with the powershell cmdlets. Here you would just query sys.server_role_members for the login's server roles.