How to Detect SQL Database Changes

  • Article

Why It Is Important

Unauthorized SQL database changes, such as deletions and creations, are always adverse events. Deletions can frustrate users and make them unable to find the 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.

Native Auditing

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

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

  1. Define file trace location(marked red) and hit “Execute” to start a new trace.

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

sp_trace_setstatus @traceid = 1, @status = 0
  1. Execute this query in order to import the trace into a database table:
USE DBname
SELECT * INTO tablename FROM ::fn_trace_gettable('C:\path\file.trc', DEFAULT)
GO
  1. 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

6. Inspect “TextData” column for an 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.

https://img.netwrix.com/landings/howtofriday/sql_native.png

Real Life Use Case

View

Credits

Originally posted - https://www.netwrix.com/how_to_detect_changes_in_sql_database.html