Auditing Your SQL Instance on the Cheap

I had some great questions after my SQL 101 session at PASS Summit last week about auditing access and actions on SQL Server. I’ve recovered enough that I can now coherently share what I’ve set up for some free1 rudimentary auditing of SQL Server.

We use Redgate’s SQL Monitor to keep tabs on our SQL environment. We started initially with this extended events session to detect database changes for a monitoring alert. We quickly realized that we probably wanted a record of what triggered the alert and made a few changes. Instead of just letting SQL Monitor query the extended events session we deployed tables to our local helper db to store any detected changes. We then updated the SQL Monitor alert to check the table itself rather than the extended events sessions directly for recent events. In addition to having a record of the changes stored for research (we clean up the records after a month or so) it also gave us the chance to tune them to be less noisy. For example we don’t particularly need to be alerted when our deployment process makes changes: we have a record of it if we need to determine what happened but generally we expect those changes and don’t need to hear about them (at least not from the monitoring process).

Once we’d done that it seemed natural to make the same modifications to a similar monitoring alert for security changes. Of course you don’t have to run a monitoring tool to use these (although it does generally make it easier). A local SQL agent job could be run to check the table and send email alerts etc. The other question that comes along with security events is “Who is supposed to have access?” So lastly we implemented a once a day poll of the instance and database principals that could serve as a point in time reference of who had access.

/* We collect the results daily and haven't been too concerned
about lost history if a particular instance fails occasionally */
DELETE FROM
dbo.secPoll;
CREATE TABLE ##userQuery
(
serverName VARCHAR(255)
, databaseName VARCHAR(255)
, databaseRoleName VARCHAR(255)
, memberName VARCHAR(255)
, loginType VARCHAR(50)
, status VARCHAR(50)
);
DECLARE @theTime DATETIME2(0) = GETDATE();
/* dynamic SQL for collecting DB permissions for each instance using Aaron Bertrands sp_foreachdb
but you'd be better off incorporating the new procedure:
https://sqlblog.org/2020/08/04/reliable-flexible-replacement-sp_msforeachdb */
EXEC master.dbo.sp_foreachdb @command = N'USE[?];
INSERT INTO ##userQuery (serverName
, databaseName
, databaseRoleName
, memberName
, loginType
, status)
SELECT @@SERVERNAME AS serverName, DB_NAME() AS databaseName
, DP1.name AS DatabaseRoleName
, ISNULL(DP2.name, ''No members'') AS MemberName
, CASE DP2.type
WHEN ''U'' THEN ''WinLogin''
WHEN ''S'' THEN ''SQLLogin''
WHEN ''G'' THEN ''WinGroupLogin''
ELSE ''UNKNOWN''
END AS LoginType
, CASE WHEN l.name IS NULL THEN ''Orphan'' ELSE ''N/A'' END AS [status]
FROM
[?].sys.database_role_members AS DRM
RIGHT OUTER JOIN [?].sys.database_principals AS DP1 ON DRM.role_principal_id = DP1.principal_id
LEFT OUTER JOIN [?].sys.database_principals AS DP2 ON DRM.member_principal_id = DP2.principal_id
LEFT OUTER JOIN master.sys.syslogins l ON l.sid = DP2.sid
WHERE
DP1.type = ''R''
AND DP2.name IS NOT NULL
AND DP2.name <> ''dbo''
AND DP2.type IN (''G'', ''U'', ''S'')'
, @exclude_list = N'tempdb,model'
, @print_dbname = 1;
/* union all the db permissions with the instance permissions
and stuff it into the local table */
INSERT INTO dbo.secPoll (serverName
, databaseName
, databaseRoleName
, memberName
, loginType
, status
, dateDetected)
SELECT serverName
, databaseName
, databaseRoleName
, memberName
, loginType
, status
, @theTime AS dateDetected
FROM ##userQuery
WHERE memberName NOT LIKE '##%'
UNION
SELECT @@SERVERNAME AS serverName
, 'N/A' AS databaseName
, CASE WHEN b.sysadmin = 1 THEN
'sysadmin'
WHEN b.securityadmin = 1 THEN
'securityadmin'
WHEN b.serveradmin = 1 THEN
'serveradmin'
WHEN b.setupadmin = 1 THEN
'setupadmin'
WHEN b.processadmin = 1 THEN
'processadmin'
WHEN b.diskadmin = 1 THEN
'diskadmin'
WHEN b.dbcreator = 1 THEN
'dbcreator'
WHEN b.bulkadmin = 1 THEN
'bulkadmin'
ELSE
'Public'
END AS ServerRole
, a.name AS MemberName
, CASE WHEN a.type_desc = 'SQL_LOGIN' THEN
'SQLLogin'
WHEN a.type_desc = 'WINDOWS_GROUP' THEN
'WinGroupLogin'
ELSE
'WinLogin'
END AS LoginType
, CASE WHEN a.is_disabled = 1 THEN
'Disabled'
WHEN a.is_disabled = 0 THEN
'Enabled'
WHEN a.is_disabled IS NULL THEN
'Error'
END AS Status
, @theTime AS dateDetected
FROM sys.server_principals AS a
JOIN master.dbo.syslogins AS b ON a.sid = b.sid
WHERE a.name NOT LIKE '##%'
ORDER BY serverName
, memberName;
DROP TABLE ##userQuery;

The natural conclusion of gathering metrics is to drive informed action/decision making so we started gathering up all the drift and security events via SSIS and plunking it in our central database so we could make it easily available for review but others. If we start to see an excessive amount of failed logins on a SQL instance we notify our info sec team. They can then review the security audit report for that SQL instance to to get a sense of what is happening and if they need to take any action (or if we need to tune our alert better). Similarly when they get requests to access data sources they can research to see who else has been granted permissions to the same instance or database as well as check to see what other data sources the user might have access to.

This is all a good way to implement something with no budget but if auditing and/or security auditing specifically is something that is important to your organization then the next step is to start researching more robust tooling!

1. You do get what you pay for.