By: K. Brian Kelley | Comments (2) | Related: 1 | 2 | 3 | > Security
Problem
I want to determine who the local administrators are on each of my SQL Servers. Basically, I'd like a nightly job to run against each of my SQL Servers and produce a list of admins for each server for my review. How can I do this?
Solution
If you're looking for an interactive solution using PowerShell to determine membership of a local group on a server, then PowerShell.com has an excellent script that does everything you need. It is written by Aleksandar Nikoliæ, a PowerShell MVP. One of the things I like about it is it retrieves the adsPath property for the group, which allows us to determine whether the members of the group are for the local computer or the domain. Unfortunately, the information adsPath returns for us is a text string which contains the domain/computer name lumped together with the user or group name. That's why this script makes use of the split method to separate that information and report it back properly.
The limitation of this script is it requires interactive input and it only hits one server at a time. If you're wanting to run a batch job against multiple SQL Servers, we can use it as a starting point, but there are some changes we have to make to get things to work.
First, we have to remove the lines that take interactive input. The lines that use the cmdlet Read-Host are the ones that tell PowerShell to prompt the user for information. Those have to go. Second, we need to set the group, which will be simply "Administrators" because we're looking for local Administrators on each of the SQL Server. we accomplish this by:
$localgroupName = "administrators"
Third, we also want to retrieve a list of Windows servers to check and a simple text file with a server name on each line will do. For instance, our text file might look like this:
HR-SQL.mydomain.com Sales-SQL.mydomain.com Test-SQL.mydomain.com
To read this file and process each entry in turn, we'll wrap the logic that retrieves the group list using a foreach loop and use the cmdlet Get-Content to retrieve the server names. So effectively, we need to do this:
foreach ($computerName in Get-Content "$filesPath\SQLServers.txt")
{
# Code to retrieve group information
}
The $filesPath is a variable which contains the path to the text file holding the names of our servers. Based on this sample code, the text file would be named SQLServers.txt. Like with $localgroupName, this would need to be set before the foreach loop. You'll be able to see it clearly in our final solution.
Fourth, and finally, we'll want to write out findings somewhere, because writing to the console doesn't do us much good for a batch job that runs wtihout interaction. We can use the Export-CSV cmdlet to store the results in .CSV files (readable by Microsoft Excel, for instance) and in order to ensure we write only the information we absolutely need, we will also use the Select-Object cmdlet to filter what we have. Putting these two commands together and using the pipeline function, we get this:
$members | Select-Object Name,Domain,Class | export-csv "$filesPath\$computername.csv"
In this case, the PowerShell script will write a separate .CSV file for each server using the server name as the basis for the file name.
If we put all this together, incorporating the changes, we'll have a final script that does the job for us:
# Adapted from: http://powershell.com/cs/media/p/3215.aspx
# List local group members on the local or a remote computer
$localgroupName = "administrators"
$filesPath = "C:\scripts\audit"
foreach ($computerName in Get-Content "$filesPath\SQLServers.txt")
{
if([ADSI]::Exists("WinNT://$computerName/$localGroupName,group")) {
$group = [ADSI]("WinNT://$computerName/$localGroupName,group")
$members = @()
$Group.Members() |
% {
$AdsPath = $_.GetType().InvokeMember("Adspath", 'GetProperty', $null, $_, $null)
# Domain members will have an ADSPath like WinNT://DomainName/UserName.
# Local accounts will have a value like
# WinNT://DomainName/ComputerName/UserName.
$a = $AdsPath.split('/',[StringSplitOptions]::RemoveEmptyEntries)
$name = $a[-1]
$domain = $a[-2]
$class = $_.GetType().InvokeMember("Class", 'GetProperty', $null, $_, $null)
$member = New-Object PSObject
$member | Add-Member -MemberType NoteProperty -Name "Name" -Value $name
$member | Add-Member -MemberType NoteProperty -Name "Domain" -Value $domain
$member | Add-Member -MemberType NoteProperty -Name "Class" -Value $class
$members += $member
}
# The back tick (`) tells PowerShell the command continues on the next line
$members | Select-Object Name,Domain,Class `
| export-csv "$filesPath\$computername.csv"
}
else {
Write-Warning `
"Local group '$localGroupName' doesn't exist on computer '$computerName'"
}
}
Of course, ensure you run the script under the context of a user account that has the ability to check the membership of the administrators group on all the servers you have listed in the text file.
Next Steps
- Check out these other PowerShell tips
- Check out these other Security tips
About the author
K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
