SQL Server Monitoring with PowerShell for Database Security

By: Alejandro Cobar | Updated: 2020-10-12 | Comments | Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | > Monitoring

Problem

One of the many roles that SQL Server DBAs must play, is the role of "security police" for the instances we support. If we have dozens or hundreds of instances under our care, wouldn't it be useful to know which logins are created in our instances and the server roles that they currently have? That way we can get a first look at any particular login that either "doesn't feel right", simply isn't supposed to be in the instance or has a particular role assigned. One of the most common mistakes is to have logins for individual users with the sysadmin server role, but this PowerShell script will help you concentrate this information in one place for analysis.

Solution

This module will gather information about the logins from all the specified instances. It will connect with each server in inventory.MasterServerList and capture the data for each of these instances. The connection to each SQL Server instance will be based on the settings in the inventory.MasterServerList table.

Prerequisites

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

Database Objects

For this particular module, two tables will be created (in case they don't exist). The first table will store the information of the logins found in the instance and the second table will store the sever roles that each particular login has assigned (which can be more than 1, so I put them in a separate table to normalize the result set).

Tables

inventory.Logins - Stores the information of the logins found in each SQL Server instance.
- loginId: Primary Key of the table, and a System generated one.
- serverId: Serverid ties back to inventory.MasterServerList.
- name: Login name.
- type: Ttype of login [SQL Login or Windows Login].
- create_date: Datetime when the login was created.
- modify_date: Datetime when the login was last modified.
- default_database_name: Name of the database that the user will connect to if a database name isn't provided when in a connection string.
- is_disabled: If the user is disabled or not.
- data_colletion_timestamp: Datetime when the PowerShell script was executed to populate the table.

inventory.LoginsRoles - Stores the server roles assigned to each particular login.
- loginId: Reference value that points to the Primary Key of the inventory.Logins table.
- server_role: Name of the role assigned to the login.
- data_collection_timestamp: Datetime when the PowerShell script was executed to populate the table.

Important Note:

There will be times when you simply don't want to collect this information from all the registered instances you have in your Master Server List. To address this, simply modify the query text inside the variable $instanceLookupQuery within the PowerShell script and you should be good to go.

PowerShell Script

The PowerShell script that creates the above objects and inserts data into the tables inventory.Logins and inventory.LoginsRoles is called:

Get-MSSQL-Instance-Logins.ps1

The script has some validations that will help you check if some key elements are missing for the script to run successfully. For instance, it will confirm that the inventory.MasterServerList table exists and that it has at least 1 active instance registered to be able to have something to work with.

If you have followed along with the other modules, you will notice that we have been storing all objects 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 following PowerShell script to specify the new folder location.

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-Logins.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-Logins.ps1"

Option 3

Schedule this as a SQL Server Agent Job to run on a regular basis.

Option 4

Schedule this as a Windows Task Scheduler job to run on a regular basis.

Check Creation of Database and Objects

After running the PowerShell script, we can see the objects that are created.

If we query the inventory.Logins and inventory.LoginsRoles tables, we can see the data that has been collected.

inventory.Logins

inventory.LoginsRoles

For demonstration purposes, in my instance with serverId = 2, I have granted my account (DESKTOP-QUDLLRR\aleja) sysadmin, serveradmin and dbcreator roles. That's why you can see that the loginId 12 has 3 entries in this table.

Note: The PowerShell script will store only the information from the last execution, no historical data is retained. If you'd like to keep information from previous executions, you would have to modify the script and adapt it to your particular use case.

Checking for Errors

To check for errors, query the monitoring.ErrorLog table using the following query:

SELECT *
FROM monitoring.ErrorLog
WHERE script = 'Get-MSSQL-Instance-Logins'

If you'd like to know the SQL Server instance that got the errors, you would have to issue the query like this:

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-Logins

Useful Queries

Which logins have sysadmin or serveradmin server roles?

SELECT 
   CASE WHEN msl.instance = 'MSSQLSERVER' 
      THEN msl.server_name 
      ELSE CONCAT(msl.server_name,'\',msl.instance) 
      END AS instance, 
   l.name,
   lr.server_role
FROM inventory.Logins l
JOIN inventory.LoginsRoles lr ON l.loginId = lr.loginId
JOIN inventory.MasterServerList msl ON msl.serverId = l.serverId
WHERE server_role IN ('sysadmin','serveradmin')

What server roles does each login have?

SELECT 
   CASE WHEN msl.instance = 'MSSQLSERVER' 
      THEN msl.server_name 
      ELSE CONCAT(msl.server_name,'\',msl.instance) 
      END AS instance, 
   l.name AS 'login',
   ISNULL((SELECT 1 FROM inventory.LoginsRoles WHERE loginId = l.loginId AND server_role = 'sysadmin'),0) AS 'sysadmin',
   ISNULL((SELECT 1 FROM inventory.LoginsRoles WHERE loginId = l.loginId AND server_role = 'serveradmin'),0) AS 'serveradmin',
   ISNULL((SELECT 1 FROM inventory.LoginsRoles WHERE loginId = l.loginId AND server_role = 'securityadmin'),0) AS 'securityadmin',
   ISNULL((SELECT 1 FROM inventory.LoginsRoles WHERE loginId = l.loginId AND server_role = 'processadmin'),0) AS 'processadmin',
   ISNULL((SELECT 1 FROM inventory.LoginsRoles WHERE loginId = l.loginId AND server_role = 'setupadmin'),0) AS 'setupadmin',
   ISNULL((SELECT 1 FROM inventory.LoginsRoles WHERE loginId = l.loginId AND server_role = 'bulkadmin'),0) AS 'bulkadmin',
   ISNULL((SELECT 1 FROM inventory.LoginsRoles WHERE loginId = l.loginId AND server_role = 'dbcreator'),0) AS 'dbcreator'
FROM inventory.Logins l
JOIN inventory.MasterServerList msl ON msl.serverId = l.serverId

This is a very cool one because it gives you a "bitmap" of all the roles per login, per instance. Here's a screenshot so that you can get an idea:

Download Scripts

Download the scripts for this module

Next Steps

Download the PowerShell script to collect data for SQL Server Indexes.
Check out some of the other parts of this series

About the author

Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2020-10-12

Monitoring SQL Server logins and server roles with PowerShell for Database Security