Overview
Audit and Tracing aim to track what is happening in the database. As such there are various ways how this can be accomplish.
Tracing
Tracing in SQL Server can be accomplished via SQL Profiler or Manually. SQL Profile is utility in which you can configure a job OR to configure a script which you can run manually. Let's do the 2nd part since it joins the both methods:
SQL Profiler
As I already mentioned. SQL Profiler is additional utility than SSMS and it can help you with configuring tracing jobs: Once you start it, you have to choose the following
General
- Trace Name (Name of the JOB which you will see in sys.traces)
- If you want to use template
- If you want to save the output to a file or table
Events Selects
Here you can select filter options, meaning what you want to be monitored. Let's assume you want to monitor all DML on one table: TABLE_TEST_1
In that case, you have to select:
- SQL:StmtCompleted. That will track ALL user DMLs
- SP:Completed. That will track any Stored procedure which might be executed on that table
- RPC:Completed
On that second tab you will find also a button called: “Column Filters” Here you can configure the database and the table:
Column Filters
In that view, configure:
- Database Name: Like: “Database Name”
- TextData: “INSERT INTO TABLE_TEST_1%”, “UPDATE TABLE_TEST_1%” and “DELETE TABLE_TEST_1%”. That will trace any DML on that table.
After you specify that, start the job. Once you start it, stop it and export the job to a script. It should look something like that:
/****************************************************/ /* Created by: SQL Server 2008 Profiler */ /* Date: 16/10/2019 17:15:23 */ /****************************************************/ -- Create a Queue declare @rc int declare @TraceID int declare @maxfilesize bigint set @maxfilesize = 5 <- Edit this number or the trace file will be only 5 MB big. -- Please replace the text InsertFileNameHere, with an appropriate -- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension -- will be appended to the filename automatically. If you are writing from -- remote server to local drive, please use UNC path and make sure server has -- write access to your network share exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL <- Add the FileName location: (e.g. E:\traceTesttable.trc) if (@rc != 0) goto error -- Client side File and Table cannot be scripted -- Set the events declare @on bit set @on = 1 exec sp_trace_setevent @TraceID, 10, 7, @on exec sp_trace_setevent @TraceID, 10, 15, @on exec sp_trace_setevent @TraceID, 10, 8, @on exec sp_trace_setevent @TraceID, 10, 16, @on exec sp_trace_setevent @TraceID, 10, 48, @on exec sp_trace_setevent @TraceID, 10, 64, @on exec sp_trace_setevent @TraceID, 10, 1, @on exec sp_trace_setevent @TraceID, 10, 9, @on exec sp_trace_setevent @TraceID, 10, 17, @on exec sp_trace_setevent @TraceID, 10, 25, @on exec sp_trace_setevent @TraceID, 10, 41, @on exec sp_trace_setevent @TraceID, 10, 49, @on exec sp_trace_setevent @TraceID, 10, 10, @on exec sp_trace_setevent @TraceID, 10, 18, @on exec sp_trace_setevent @TraceID, 10, 26, @on exec sp_trace_setevent @TraceID, 10, 50, @on exec sp_trace_setevent @TraceID, 10, 66, @on exec sp_trace_setevent @TraceID, 10, 3, @on exec sp_trace_setevent @TraceID, 10, 11, @on exec sp_trace_setevent @TraceID, 10, 35, @on exec sp_trace_setevent @TraceID, 10, 51, @on exec sp_trace_setevent @TraceID, 10, 4, @on exec sp_trace_setevent @TraceID, 10, 12, @on exec sp_trace_setevent @TraceID, 10, 60, @on exec sp_trace_setevent @TraceID, 10, 13, @on exec sp_trace_setevent @TraceID, 10, 6, @on exec sp_trace_setevent @TraceID, 10, 14, @on exec sp_trace_setevent @TraceID, 45, 7, @on exec sp_trace_setevent @TraceID, 45, 55, @on exec sp_trace_setevent @TraceID, 45, 8, @on exec sp_trace_setevent @TraceID, 45, 16, @on exec sp_trace_setevent @TraceID, 45, 48, @on exec sp_trace_setevent @TraceID, 45, 64, @on exec sp_trace_setevent @TraceID, 45, 1, @on exec sp_trace_setevent @TraceID, 45, 9, @on exec sp_trace_setevent @TraceID, 45, 17, @on exec sp_trace_setevent @TraceID, 45, 25, @on exec sp_trace_setevent @TraceID, 45, 41, @on exec sp_trace_setevent @TraceID, 45, 49, @on exec sp_trace_setevent @TraceID, 45, 10, @on exec sp_trace_setevent @TraceID, 45, 18, @on exec sp_trace_setevent @TraceID, 45, 26, @on exec sp_trace_setevent @TraceID, 45, 50, @on exec sp_trace_setevent @TraceID, 45, 66, @on exec sp_trace_setevent @TraceID, 45, 3, @on exec sp_trace_setevent @TraceID, 45, 11, @on exec sp_trace_setevent @TraceID, 45, 35, @on exec sp_trace_setevent @TraceID, 45, 51, @on exec sp_trace_setevent @TraceID, 45, 4, @on exec sp_trace_setevent @TraceID, 45, 12, @on exec sp_trace_setevent @TraceID, 45, 60, @on exec sp_trace_setevent @TraceID, 45, 5, @on exec sp_trace_setevent @TraceID, 45, 13, @on exec sp_trace_setevent @TraceID, 45, 29, @on exec sp_trace_setevent @TraceID, 45, 61, @on exec sp_trace_setevent @TraceID, 45, 6, @on exec sp_trace_setevent @TraceID, 45, 14, @on exec sp_trace_setevent @TraceID, 45, 15, @on exec sp_trace_setevent @TraceID, 41, 7, @on exec sp_trace_setevent @TraceID, 41, 15, @on exec sp_trace_setevent @TraceID, 41, 55, @on exec sp_trace_setevent @TraceID, 41, 8, @on exec sp_trace_setevent @TraceID, 41, 16, @on exec sp_trace_setevent @TraceID, 41, 48, @on exec sp_trace_setevent @TraceID, 41, 64, @on exec sp_trace_setevent @TraceID, 41, 1, @on exec sp_trace_setevent @TraceID, 41, 9, @on exec sp_trace_setevent @TraceID, 41, 17, @on exec sp_trace_setevent @TraceID, 41, 25, @on exec sp_trace_setevent @TraceID, 41, 41, @on exec sp_trace_setevent @TraceID, 41, 49, @on exec sp_trace_setevent @TraceID, 41, 10, @on exec sp_trace_setevent @TraceID, 41, 18, @on exec sp_trace_setevent @TraceID, 41, 26, @on exec sp_trace_setevent @TraceID, 41, 50, @on exec sp_trace_setevent @TraceID, 41, 66, @on exec sp_trace_setevent @TraceID, 41, 3, @on exec sp_trace_setevent @TraceID, 41, 11, @on exec sp_trace_setevent @TraceID, 41, 35, @on exec sp_trace_setevent @TraceID, 41, 51, @on exec sp_trace_setevent @TraceID, 41, 4, @on exec sp_trace_setevent @TraceID, 41, 12, @on exec sp_trace_setevent @TraceID, 41, 60, @on exec sp_trace_setevent @TraceID, 41, 5, @on exec sp_trace_setevent @TraceID, 41, 13, @on exec sp_trace_setevent @TraceID, 41, 29, @on exec sp_trace_setevent @TraceID, 41, 61, @on exec sp_trace_setevent @TraceID, 41, 6, @on exec sp_trace_setevent @TraceID, 41, 14, @on -- Set the Filters declare @intfilter int declare @bigintfilter bigint exec sp_trace_setfilter @TraceID, 1, 0, 6, N'INSERT INTO %TABLE_TEST_1%' exec sp_trace_setfilter @TraceID, 1, 1, 6, N'update %TABLE_TEST_1%' exec sp_trace_setfilter @TraceID, 1, 1, 6, N'delete %TABLE_TEST_1%' exec sp_trace_setfilter @TraceID, 35, 0, 6, N'DATABASE_NAME' -- Set the trace status to start exec sp_trace_setstatus @TraceID, 1 -- display trace id for future references select TraceID=@TraceID goto finish error: select ErrorCode=@rc finish: go
Once you execute that job, you should receive the ID of the job. (Usually “1”). You can contract the job status via the following commands:
--START EXEC sp_trace_setstatus @traceid=1, @status=1 --STOP EXEC sp_trace_setstatus @traceid=1, @status=0 --CLOSE TRACE EXEC sp_trace_setstatus @traceid=1, @status=2