Problem:
- Find every SQL Server on the network
- Determine who has what server roles
- For each database see who has access
- Bonus: If a AD group has access then who is in that group!
Find SQL servers using AD & SPN
First we need our trust SMO:
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')To get all the servers we can use a bit of magic from AD, namely the SPN (server principal name):
function Get-SqlServers($adroot){$result = @()$root = New-Object DirectoryServices.DirectoryEntry $adroot $searcher = New-Object DirectoryServices.DirectorySearcher$searcher.SearchRoot = $root#Since SQL Server registers an SPN as part of it's setup this should bring back every server on the network$searcher.Filter = "(servicePrincipalName=MSSQL*)"#specify a page size so it doesn't just return 1000, setting anything greater than 1000 will result in multiple pages returning$searcher.PageSize = 1001$DomainSPNs = $searcher.FindAll()foreach($obj in $DomainSPNs ){#we're only interest in records MSSql* properties#each record could have many properties we don't care about so filter on MSSql. Also exclude other things here like objects not starting with L00$result += $obj.Properties.serviceprincipalname | where {$_ -like "MSSql*" -and $_ -notlike "*L00*"} | select @{N="Server";E={$_.split("/")[1].split(":")[0].split(".")[0]}}, @{N="Port";E={$_.split("/")[1].split(":")[1]}}, @{N="SPN";E={$_}}, @{N="Name";E={$obj.Properties.name}} | Sort-Object -Property Server -Unique}return $result }
Calling this with your domain root : Get-SqlServers "'LDAP://dc=domain,dc=com'" will bring back a list of Server, Port, actual SPN and the name of the 'thing' that had the SPN -usually the service account if the SQL instance is using one.
Server roles
With the hard part of getting the servers done then it's time to find who has what role (sysadmin etc.)
function Get-SqlRoles($servername)
{
$server = new-object ('Microsoft.SqlServer.Management.Smo.Server') $servername
$result = @()
foreach($role in $server.Roles)
{
$rm = $role.EnumMemberNames()
if($rm)
{
$result += $rm | Select @{N="Server";E={$servername}}, @{N="Role";E={$role.Name}}, @{N="Member";E={$_}}
}
else
{
$result += select @{N="Server";E={$servername}}, @{N="Role";E={$role.Name}}, @{N="Member";E={"no member"}}
}
}
return $result
}
Returning a list of server, role, and member.
Databases and their users
There's a few things going on here
- Get every database
- Get every user
- If they're a WindowsGroup then find who's in that group.
It's not the most elegant of functions and if users are in multiple groups then you'll see the same user multiple times, but when security asks 'Who has access to the HR database?' You can tell them the actual people rather than go hunting through AD.
function Get-SqlDbUsers($servername)
{
$server = new-object ('Microsoft.SqlServer.Management.Smo.Server') $servername
$dbs = $server.Databases
$result= @()
foreach($db in $dbs)
{
"getting users for $db"
foreach($u in $db.Users )
{
$result += $u.EnumRoles() | select @{N="Name";E={$servername}},@{N="Database";E={$db.Name}}, @{N="User";E={$u.Name}}, @{N="Type";E={$u.LoginType}}, @{N="Role";E={$_}}, @{N="ADGroup";E={"N/A"}}
if($u.LoginType -eq "WindowsGroup")
{
#go get from AD
"getting AD group members for $($u.Name)"
foreach($role in $u.EnumRoles())
{
#note the -Recursive to get sub members
$result += get-adgroupmember $u.Name.Split("\")[1] -Recursive | select @{N="Server";E={$servername}},@{N="Database";E={$db.Name}}, @{N="User";E={$_.Name}}, @{N="Type";E={$u.LoginType}}, @{N="Role";E={$role}}, @{N="ADGroup";E={$u.Name}}
}
}
}
}
return $result
}
It can all be wrapped up in a big foreach and exported to CSV.
$sqlServers = Get-SqlServers 'LDAP://dc=domain,dc=com'
foreach($server in $sqlServers)
{
try
{
$sqldbusers += Get-SqlDbUsers $server.Name
}
catch{
$fail = New-Object PSCustomObject
$fail | Add-Member -type NoteProperty -name Server -Value $server.Name
$fail | Add-Member -type NoteProperty -name Reason -Value "unable to access server for roles"
$failures += $fail
}
try
{
$sqlroles += Get-SqlRoles $server.Name
}
catch{
$fail = New-Object PSCustomObject
$fail | Add-Member -type NoteProperty -name Server -Value $server.Name
$fail | Add-Member -type NoteProperty -name Reason -Value "unable to access server for users"
$failures += $fail
}
}
$resultPath = "c:\temp\SqlAudit\"
$stampstring = (get-date).ToString("yyyyMmdd")
$resultPath += $stampstring
New-Item -Path $resultPath -ItemType directory -Force
$failures | select Server, Reason | Export-Csv "$($resultPath)\SqlAudit_failures.csv"
$sqlServers | Export-Csv "$($resultPath)\SqlAudit_Servers.csv"
$sqlroles | Export-Csv "$($resultPath)\SqlAudit_Roles.csv"
$sqldbusers | Export-Csv "$($resultPath)\SqlAudit_Permissions.csv"
