How to Check SysAdmin Role in SQL Server with PowerShell


By:   |   Updated: 2020-08-20   |   Comments   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | More > Monitoring


Is the Database the Culprit of Your Application Issues?

Free MSSQLTips Webinar: Is the Database the Culprit of Your Application Issues?

When you're troubleshooting application performance issues, have you ever found the problem residing a few layers deep in the database or not at all? How long did it take you to find and fix the issue? Don't worry if you said "a while". Learn how to solve performance problems fast.


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.

database 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.

query results

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

Next Steps

Check out the other parts of this series



Last Updated: 2020-08-20


get scripts

next tip button



About the author





Comments For This Article





download





Recommended Reading

Monitoring SQL Server with PowerShell Core Object Setup

Monitoring SQL Server with PowerShell Instance Data Collection

SQL Server Agent Job Monitoring with PowerShell

SQL Server Backup Monitoring with PowerShell

SQL Server Agent Job Monitoring for Last Execution with PowerShell








get free sql tips
agree to terms


Learn more about SQL Server tools