How to detect SQL database changes

Introduction Unauthorized SQL database changes, such as deletions and creations, are always adverse events. Deletions can frustrate u...

Introduction

Unauthorized SQL database changes, such as deletions and creations, are always adverse events. Deletions can frustrate users and make them unable to find information they need. Creations may also be troublesome for IT departments, because they decrease the amount of free space on a server. Early detection of unauthorized changes may help IT departments avoid hours of troubleshooting.

http://www.netwrix.com/how_to_detect_sql_database_changes.html

Steps (7 total)

1

Run MS SQL Management Studio

un MS SQL Management Studio → Connect to database you want to audit → Click “New Query” → Copy the following code into “New Query” box:

2

Query Code

DECLARE @RC int, @TraceID int, @on BIT
EXEC @rc = sp_trace_create @TraceID output, 2, N'C:\path\file'
SELECT RC = @RC, TraceID = @TraceID
-- Follow Common SQL trace event list and common sql trace
-- tables to define which events and table you want to capture
SELECT @on = 1
EXEC sp_trace_setevent @TraceID, 128, 1, @on
-- (128-Event Audit Database Management Event, 1-TextData table column)
EXEC sp_trace_setevent @TraceID, 128, 11, @on
EXEC sp_trace_setevent @TraceID, 128, 14, @on
EXEC sp_trace_setevent @TraceID, 128, 35, @on
EXEC @RC = sp_trace_setstatus @TraceID, 1
GO

3

Start the Trace

Define file trace location(instead of 'C:\path\file') and hit “Execute” to start a new trace.

4

Execute this query to stop the trace when you want to audit data:

sp_trace_setstatus @traceid = 1, @status = 0

5

Execute this query in order to import the trace into database table:

USE DBname
SELECT * INTO tablename FROM ::fn_trace_gettable('C:\path\file.trc', DEFAULT)
GO

6

Execute this query in order to view top 1000 rows of trace data:

SELECT TOP 1000 [TextData] ,[HostName] ,[LoginName] ,[StartTime] ,[EndTime] ,[ServerName] ,[EventClass]
FROM [DBname].[dbo].[tablename]
WHERE DBname.dbo.tablename.TextData IS NOT NULL

7

Investigate the trace data

Expand

nspect “TextData” column for event: CREATE DATABASE, Drop Database, Alter Database in order to know what database was changed, to find out who changed it refer to “LoginName” column, and “StartTime” to know when.

Conclusion

These steps work well even for Microsoft SQL Express version.
More Events and Tables' codes here - http://www.netwrix.com/free_guide_for_sql_server_auditing.html

References

• SQL Server Auditing Quick Reference Guide
• How to Detect SQL Database Changes
• Netwrix Auditor for SQL Server