Many of our Virtual-DBA clients need to complete audits for compliance with regulations. SOX (Sarbanes Oxley) is a common audit that our customers need to successfully complete. A common question the auditors ask is,
“How do you know a user has been granted elevated privileges?”
How do we do this? Let’s get started. The use of WMI queries in alerts is, how I should put this, lightly documented. The alert we’ll describe requires that the SQL Server background trace (Flight Recorder trace) be running. This trace is low overhead and I recommend all our customers run it at startup.
Start with SQL Server Agent right click on alerts and choose “New Alert”. Call it something like “SysadminGiven” then select WMI Event Alert as the type. The Namespace will be something like .rootMicrosoftSqlServerServerEventsMSSQLSERVER (you may need to adjust this on your system). The Query that is the heart of the process is really quite simple:
SELECT * from AUDIT_ADD_LOGIN_TO_SERVER_ROLE_EVENT
What this does is fire an alert any time a login is added to a Server Role. This script can be used to create the alert. It’s been tested on SQL 2008 through SQL 2014.
/****** Object: Alert [SysadminGiven] Script Date: 04/05/2013 13:43:40 ******/
EXEC msdb.dbo.sp_add_alert @name=N'SysadminGiven',
@notification_message=N'A Server Role has been updated on
$(ESCAPE_NONE(WMI(ComputerName))) by $(ESCAPE_NONE(WMI(LoginName))) to User: $(ESCAPE_NONE(WMI(TargetLoginName))) for Role: $(ESCAPE_NONE(WMI(RoleName)))',
@wmi_query=N'select * from AUDIT_ADD_LOGIN_TO_SERVER_ROLE_EVENT',
In the previous step we have capture the event we want to track and we have, however we’ll need to provide a bit more intelligence to do something with the alert. In this case we want it to send an email to a list of users when a login is added to the sysadmin roll. To accomplish this we’ll fire off a job from the alert that has some intelligence built in. This is as simple as going to the response tab and selecting “Execute Job”.
We’ll use sys.fn_trace_gettable function to query the Flight recorder trace with a where clause that contains “’Audit Add Login to Server Role Event’” and subclass_name IN ( ‘add’, ‘Grant database access’ ) and AND RoleName = ‘sysadmin’.
SELECT TE.name AS [EventName] ,
FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1
FROM sys.fn_trace_getinfo(NULL) f
WHERE f.property = 2
)), DEFAULT) T
JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id
AND v.subclass_value = t.EventSubClass
WHERE te.name = 'Audit Add Login to Server Role Event'
AND v.subclass_name IN ( 'add', 'Grant database access' )
AND RoleName = 'sysadmin'
I integrated the above into a stored procedure I then added some conditional logic to decide when to send an e-mail to the SOX Admin. Your needs may require that the data be inserted into a logging table for history retention, these steps can easily be added here by modifying the SQL Statement.
You’ll want to customize this portion to meet the requirements you have so I’ll leave the conditional logic and formatting as an exercise for the reader.
Our experience has shown us SQL Server provides the tools needed to meet audit requirements. This alert provides a low overhead way to detect an elevation of privileges without having to install additional software or tracking technologies.