How to audit instance changes and Profiler usage?

Question

Greetings. I have a vendor with unlimited access to our most important SQL box, and that's not about to change. The best I can do here is track whatever they do. I've already got some tools in place for DB changes, as well as user/ login modifications.

What I'm really lacking tough is a good way to monitor changes to the instance itself, as well Profiler usage.

SQL Audit can track Profiler usage, but it returns way more info than I want w no way to filter on what it's capturing. I'm also not sure it can track instance changes?

I was focused on the default trace, but then discovered it doesn't capture all instance changes (linked servers, settings on the Security tab of server properties, settings on the Processors tab of server properties, etc).

Any ideas on how I can track this info?

Thanks!

Answer 1

Hi，chrisrdba

SQL Audit can track Profiler usage, but it returns way more info than I want w no way to filter on what it's capturing. I'm also not sure it can track instance changes?

Through SQL Server Audit, you can create Server Audit Specification and Database Audit Specification.

You can refer to

Create a server audit and database audit specification

SQL Server Audit Action Groups and Actions

I was focused on the default trace, but then discovered it doesn't capture all instance changes (linked servers, settings on the Security tab of server properties, settings on the Processors tab of server properties, etc).

Since the execution operations on the linked server are actually performed locally on the server, it is not possible to remotely track the execution details. However, you can track commands related to the Linked server by tracing OLEDB eventsthrough Sql profiler.

You can filter the target object of batch commands or remote calls by linked server name as shown in the figure：

Events with Linked servername are quite rare and are mainly concentrated in OLEDB events; you need to combine other event events with OLEDB events.

Log： 

Best regards

Mikey Qiao

Answer 2

I have really been overcomplicating this whole thing. All that's needed is an old school trace w the right filters.

Below gets me everything I need for the instance itself, still need to mod for Linked Servers.

As mentioned earlier I already have what I need for DB changes (Extended Events), and well as login/ user mods (trace).

exec sp_trace_setfilter @TraceID, 1, 0, 6, N'%sp_configure%'

exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%exec sp_trace_setStatus%'

exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%ALTER SERVER CONFIGURATION SET %'

exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%EXEC xp_instance_regwrite %'

Answer 3

disregard -- double post