How to Check SysAdmin Role in SQL Server with PowerShell

Problem

As proactive SQL Server DBAs, we must have a way to know which accounts reside within our systems and which accounts have sysadmin level privileges. There might be individual accounts with this elevated privilege and we must be able to detect them. You know the risks of having logins with a high privilege level, so the objective of this module is to give you a quick glance across all the instances under your support, so that you can act accordingly.

Solution

This PowerShell module gathers information about logins that have sysadmin permissions for a SQL Server instance. This will allow you to see if any of your SQL Server instances have logins that should not have these rights and allow you to remove sysadmin rights where needed.

Prerequisites

In order to use this module, you need to create the core objects found here.

Database Objects

One table will be created (in case it doesn’t exist), and this table will store information about accounts with sysadmin privileges from each instance that is being monitored.

This is the table structure. If you want to add more items to this table, make sure to adjust the PowerShell script for this module.

inventory.SA
- serverId – serverid ties back to inventory.MasterServerList
- name – SQL Server login name
- type – type of account
- create_date – date it was created
- modify_date – date it was modified
- default_database – the default database for the login
- is_disabled – if login is enabled or not
- data_collection_timestamp – when data was last collected.

PowerShell Script

The PowerShell script that creates the above object and inserts data into the inventory.SA table is called:

Get-MSSQL-Instance-SA.ps1

If you have read other modules, you will notice that we have been storing these scripts in "C:\temp", but you can use any folder you want. If you make a change to the central folder location, you will need to edit the first line in the above PowerShell script to specify the folder.

How to Use

Navigate to the folder where you created the files and you can run the PowerShell script as follows:

Option 1

Right click on Get-MSSQL-Instance-SA.ps1 and select Run with PowerShell

Option 2

Open a command window and navigate to the folder where you saved the above files and run

powershell "C:\temp\Get-MSSQL-Instance-SA.ps1"

Option 3

Schedule this as a SQL Server Agent Job

Option 4

Schedule this as a Windows Task Scheduler Job

Check Creation of Database and Objects

After running the PowerShell script, you should see the following objects.

If we query the inventory.SA table, we can see the data that has been collected. The script only stores information from the last execution. If you’d like to keep history, you will have to modify the script to meet your needs.

Checking for Errors

To check for errors, query the monitoring.ErrorLog table as follows:

SELECT *
FROM monitoring.ErrorLog
WHERE script = 'Get-MSSQL-Instance-SA
Query Collected Data

If you’d like to know the SQL Server instance that had an error use the following:

SELECT 
   CASE WHEN msl.instance = 'MSSQLSERVER' THEN msl.server_name ELSE 
      CONCAT(msl.server_name,'\',msl.instance) END AS instance,
   e.script,
   e.message,
   e.error_timestamp
FROM monitoring.ErrorLog e
JOIN inventory.MasterServerList msl ON msl.serverId = e.serverId
WHERE e.script = 'Get-MSSQL-Instance-SA'

Useful Cases

Here are some helpful queries to use with this data.

Which SQL logins other than SA have sysadmin rights

If you want to get the information only of the accounts that are currently enabled, simply add to the WHERE clause the condition is_disabled = 0.

SELECT *
FROM inventory.SA
WHERE type = 'SQL_LOGIN' AND name <> 'sa'

Which domain accounts have sysadmin rights

You can also add the check to for accounts that are enabled by adding to the WHERE clause the condition is_disabled = 0.

SELECT *
FROM inventory.sa
WHERE type = 'WINDOWS_LOGIN' AND name NOT LIKE '%NT SERVICE%'

Download Scripts

Download the scripts for this module

Next Steps

Check out the other parts of this series

Resources

Community

Subscribe