How to Check SysAdmin Role in SQL Server with PowerShell
By: Alejandro Cobar | Updated: 2020-08-20 | Comments | Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | More > Monitoring
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.
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.
In order to use this module, you need to create the core objects found here.
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.
- 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.
The PowerShell script that creates the above object and inserts data into the inventory.SA table is called:
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:
- Right click on Get-MSSQL-Instance-SA.ps1 and select Run with PowerShell
- Open a command window and navigate to the folder where you saved the above files and run
- Schedule this as a SQL Server Agent Job
- 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'
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%'
Check out the other parts of this series
- Monitoring SQL Server with PowerShell Core Object Setup
- Monitoring SQL Server with PowerShell Instance Data Collection
- Monitoring SQL Server with PowerShell SQL Agent Jobs
- SQL Server Backup Monitoring with PowerShell
- SQL Server Agent Job Monitoring for Last Execution with PowerShell
- SQL Server Database and Database File Monitoring with PowerShell
About the author
View all my tips
Article Last Updated: 2020-08-20