Who can do what they want on your SQL Server?
No pressure.
None whatsoever.
There is just an Auditor standing behind you waiting for you to pull a list of all the accounts with sysadmin rights in your SQL Environment.
You can’t BinGle; the Auditor is watching.
You start to sweat.
A lot.
You excuse yourself to run to the restroom to hide the panic attack you are having.
While there you grab your phone and start googling the syntax. What are you going to do? You don’t want to check every account one by one. Even checking the sysadmin role isn’t a great help since you use a lot of AD groups and who knows who is in those! This It is going to take forever!
Or not.
Welcome to your salvation!
(other than actually having scripts and the information ready ahead of time.)
Ok, maybe not your salvation, but how about some help?
The script below identifies the accounts on your SQL Server that have full sysadmin rights, either on their own or via an Active Directory Group.
To run this, you need a few things setup first.
- A file named Instances.txt that has each instance you are going to check on its own line. Just the name, nothing more. You can see the reference to the location at the beginning of the script, just change it to wherever you put your file.
- Rights to read the AD information for the domain. This way we can get the members of any groups granted access to your SQL environment.
The output is simply the instance name followed by the list of user accounts that have SysAdmin access. If an account is part of an AD group, the group name displays after the username in parentheses. (Like this!) This way you can see if someone has access granted via multiple methods and help explain why they may still do things you don’t want even after you removed their individual account.
If you just run the script in PowerShell, you will see the results output to the screen. If you first save and then execute the script using the following syntax, you can output the results into a file to keep a history.
./scriptname.ps1 > Filename.txt
Anyhow, here’s the script. Use it and enjoy, let me know if there are any issues with it, and hopefully it will help you as you work towards protecting your data.
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
foreach ($SQLsvr in get-content "C:\temp\Instances.txt")
{
$svr = new-object ("Microsoft.SqlServer.Management.Smo.Server") $SQLsvr
write-output "================================================================================="
write-output "SQL Instance: " $svr.name
write-output "================================================================================="
$SQLLogins = $svr.logins
foreach ($SQLLogin in $SQLLogins)
{
$SQLRoles = $SQLLogin.ListMembers()
if ($SQLRoles -eq "SysAdmin"){
If ( $SQLLogin.LoginType -eq "WindowsGroup" ) {
try {
$ADGRoupMembers = get-adgroupmember $SQLLogin.Name.substring(3) -Recursive
foreach($member in $ADGRoupMembers){
$mbr=$SQLLogin.name
$Dom=$SQLLogin.name.split("\")[0]
$nm=$member.SamAccountName
write-output "$DOM\$NM ($mbr)"}
}
catch{}
}
if($SQLLogin.LoginType -ne "WindowsGroup"){ write-output $sqllogin.name}
}
}}