PowerShell -> Identify all SysAdmins in your SQL Server

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.

  1. 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.
  2. 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.

Comments 5

    1. hi there , nice script, but i found out that the script couldn’t extract the domain group which granted as sysadmin. TQ

  1. Hi , Thanks for details.

    In below piece of code how should I order by created date n DateLastModified in last seven days?


    if($role -match ‘sysadmin’)
    Write-Host “SysAdmins found: $($SQLUser.Name)” -ForegroundColor Yellow;
    $SQLUser | Select-Object
    @{label = "SQLServer"; Expression = {$SQLSvr}},

    @{label = “CurrentDate”; Expression = {(Get-Date).ToString(“yyyy-MM-dd”)}},
    @{label = "System TypeName"; Expression = {$ds.Tables[0].Rows[$i]["SystemTypeName"]}},

    @{label = “Environment Name”; Expression = {$ds.Tables[0].Rows[$i][“EnvironmentName”]}},
    @{label = "Role Name"; Expression = {$ds.Tables[0].Rows[$i]["RoleName"]}},
    Name, LoginType, CreateDate, DateLastModified, IsDisabled, IsLocked;

    if($SQLUser.DateLastModified -ge $dateToCompare)
    $SysAdminsAddedInReportingPeriod+=$SQLUser | Select-Object

    @{label = “SQLServer”; Expression = {$SQLSvr}},
    @{label = "CurrentDate"; Expression = {(Get-Date).ToString("yyyy-MM-dd")}},

    @{label = “System TypeName”; Expression = {$ds.Tables[0].Rows[$i][“SystemTypeName”]}},
    @{label = "Environment Name"; Expression = {$ds.Tables[0].Rows[$i]["EnvironmentName"]}},

    @{label = “Role Name”; Expression = {$ds.Tables[0].Rows[$i][“RoleName”]}},
    Name, LoginType, CreateDate, DateLastModified, IsDisabled, IsLocked;


    1. Post

      Sorry, but my PS isn’t good enough to quickly glance and figure it out. I would poke and play with it until I got what I was looking for. Perhaps it would require dumping the data somewhere and then sort it. I may try to figure it out once I have some time to dedicate to it.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.