How to enable DML and DDL auditing on SQL Server Standard?

Only SQL Server Enterprise Edition provides database auditing. If you want to audit your standard databases, there are 3 ways but each has its own pros and cons

Method 1

C2 Audit can be used to trace and log end user activity, such as all SQL command, login and logout, DBA activity, such as grant/deny/remove security event, configure database or server, Security events, Utility events, and serve events such as shutdown pause and start. You can enable C2 audit by below command

sp_configure 'show advanced options', 1 ;

GO

RECONFIGURE ;

GO

sp_configure 'c2 audit mode', 1 ;

GO

RECONFIGURE ;

GO

However, C2 Audit will log everything and your log can grow exponentially. If there is no space on the database server, your SQL server can fail. This option has to be used cautiously.

Method 2

Use of Insert/Delete/Trigger to track DML on your tables. This is however restricted to DML

 

Method 3 

Use of SQL Server Profiler to selectively log events on your databases.  This has to be used cautiously as well as it can cause performance issues on the server

Method 4

Use of Database Extended Events

In this article, I will discuss in details how to implement a logging mechanism with Method 3, that is SQL Profiler

What is SQL Profiler ?

Microsoft SQL Server Profiler is a graphical user interface for monitoring an instance of the Database Engine. With SQL Profiler, it is possible to capture and save data about each event to a file or table to be analyzed later. For example, you can log all DML and DDL events.

Creating a Trace Definition

We are going to monitor all DML and DDL Statements for specific accounts on all all databases except Tempdb. Services Account such as SQLServices will be excluded in the trace.

You can monitor any other events as far it does not generate lots of trace which will impact on the performance

Start SQL Profiler and select File -- > New Trace

Click on Events Selection Tab and select Show all Events and Show all Columns

 

In the Events Tree, Select Database -- > TSQL and Select SQL:BatchCompleted

Click on Column Filters and add a filter on the DatabaseName and LoginName

 

Start the Trace, ensure it is capturing the events and stop it

 

 

Create a Profile Trace Stored Procedure

Export the Script Trace Definition for SQL Server 2005 and save the SQL. File -- > Export

In the exported trace definition, find '-- Set the events' section. Copy all the lines until the next comment line which should be '-- Set the Filters'

Paste these lines into the stored procedure template after the line "add the '-- Set the events' section below"

Next find the '-- Set the Filters' section in the exported trace definition. Copy all the lines until the '-- Set the trace status to start'

Paste these lines into the stored procedure template after the line "add the '-- Set the Filters' section below"

/*

use Master

go

*/

CREATE procedure LogMgt @Folder nvarchar(200)

as

set nocount on

-- To change the traces duration, modify the following statement

declare @StopTime datetime ; set @StopTime = dateadd(yy,60,getdate())

declare @StartDatetime varchar(13) ; set @StartDatetime =

convert(char(8),getdate(),112) + '_' +

cast(replace(convert(varchar(5),getdate(),108),':','') as char(4)) --['YYYYMMDD_HHMM']

declare @rc int

declare @TraceID int

declare @TraceFile nvarchar(100)

declare @MaxFileSize bigint ; set @MaxFileSize = 500 -- The maximum trace file in megabytes

declare @cmd nvarchar(2000)

declare @msg nvarchar(200)

If right(@Folder,1)<>'\' set @Folder = @Folder + '\'

-- Check if Folder exists

set @cmd = 'dir ' +@Folder

exec @rc = master..xp_cmdshell @cmd,no_output

if (@rc != 0) begin set @msg = 'The specified folder ' + @Folder + '

does not exist, Please specify an existing drive:\folder '+ cast(@rc as

varchar(10)) raiserror(@msg,10,1) return(-1)

end

--Create new trace file folder

set @cmd = 'mkdir ' +@Folder+@StartDatetime

exec @rc = master..xp_cmdshell @cmd,no_output

if (@rc != 0) begin set @msg = 'Error creating trace folder : ' +

cast(@rc as varchar(10)) set @msg = @msg + 'SQL Server 2005 or later

instance require OLE Automation to been enabled' raiserror(@msg,10,1)

return(-1)

end

set @TraceFile = @Folder+@StartDatetime+'\trace'

exec @rc = sp_trace_create @TraceID output, 2, @TraceFile,

@MaxFileSize, @StopTime

if (@rc != 0) begin set @msg = 'Error creating trace : ' + cast(@rc as

varchar(10)) raiserror(@msg,10,1) return(-1)

end

--> Using your saved trace file, add the '-- Set the events' section below <--

--> Using your saved trace file, add the '-- Set the Filters' section below <--

--> Customization is now completed <--

-----------------------------------------------------------------------------

-- This filter is added to exclude all profiler traces.

--exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler%'

-- Set the trace status to start

exec sp_trace_setstatus @TraceID, 1 -- start trace

select 'Trace id = ', @TraceID, 'Path=', @Folder+@StartDatetime+'\'

select 'To Stop this trace sooner, execute these two commands'

select ' EXEC sp_trace_setstatus @traceid = ' , @TraceID , ', @status = 0; -- Stop/pause Trace'

select ' EXEC sp_trace_setstatus @traceid = ' , @TraceID , ', @status = 2; -- Close trace and delete it from the server'

return

go

Save the Stored Procedure

Running the Profiler Trace

Execute the Stored Procedure by running the command

Exec LogMgt ‘c:\mytrace\’

To ensure the trace is running, execute the command

 

select * FROM ::fn_trace_getinfo(default)

 

Recycling the Trace

We will create a new log file every 3 months (should be less than the retention period) and to achieve this, we need to recycle the trace by restarting it

Create Procedure recycleLogMgt

as

Declare @trcid int

Select @trcid = id from sys.traces where path like '%mytrace%'

EXEC sp_trace_setstatus @traceid =@trcid, @status=0

EXEC sp_trace_setstatus @traceid =@trcid, @status=2

exec LogMgt ‘c:\mytrace\’

Schedule the stored procedure every 3 months

Monitoring existing and previous Traces

Run the query below by specifying the file you want to monitor

select * from fn_trace_gettable('C:\Trace\20151012_0922\trace.trc', default);