Who is Updating That Table? – SQL Audit

I had a problem at work recently where a record was getting updated, and no one knew where or what was updating the record.  Our team discussed the best way to try to figure out what was happening.  The situation was if a record would be updated to active and within a ten-minute window, the record would be set back to inactive.  The system allows ad-hoc statements to run against and since it was to only a certain table, I suggested we set up a SQL Audit to track UPDATEs to the table.  The code for this is fairly simple, but since most of my colleagues don’t have exposure to SQL Audit, I figured a blog post would benefit others.

So, in this case, we are creating a Server SQL Audit that will write to D:\SQL Audit, so make sure that path exists.  Then a Database Server Audit Specification to track any UPDATEs that happen to the table.  Now, keep in mind I choose the method over running a server-side Trace or Extended Events because I knew it would capture everything without me having to worry about setting up anything else put these commands.  An important part of this is where I specify “public”.  That tells the audit to capture anybody that is updating the table.  If you want to look for a certain user or even maybe someone part of a role, you could specify that instead.

First let’s create a directory for the file to into:

$ServerName = "YourServer" 
# Change Directory as needed below
Invoke-Comannd -ComputerName $ServerName -ScriptBlock { NewItem-Item -Path "D:\SQLAudit\" -ItemType -ItemType Directory }

Now, we can create the audit objects.

USE master ;  
GO  
DECLARE @Directory = 'D:\SQLAudit\'
-- Create the server audit.
-- Change the path to a path that the SQLServer Service has access to. 
CREATE SERVER AUDIT DataModification_Security_Audit  
    TO FILE ( FILEPATH = @Directory ) ;  -- make sure this path exists
GO  
-- Enable the server audit.  
ALTER SERVER AUDIT DataModification_Security_Audit   
WITH (STATE = ON) ;  
GO  
-- Move to the target database.  
USE AdventureWorks2012 ;  
GO
-- Create the database audit specification.  
CREATE DATABASE AUDIT SPECIFICATION Audit_Data_Modification_On_Table_Sales
FOR SERVER AUDIT DataModification_Security_Audit
ADD ( UPDATE ON Schema::Sales BY public )  
WITH (STATE = ON) ;    
GO

Then, we will need to select from the audit the data we are looking for with the following data query.  In the statement clause specify what you are looking for, in my case I was looking for updates to a particular server name where the Active column was be changed.

-- Change directory as needed in WHERE clause
SELECT event_time, 
session_server_principal_name AS UserName , 
server_instance_name , 
database_name , 
object_name , 
statement 
FROM sys.fn_get_audit_file('D:\SQLAudit\*.sqlaudit', DEFAULT, DEFAULT) 
WHERE statement like '%servername%'
AND statement = '%Active%';

Once I found the culprit, which turned out to be schedule task that was only updating this one record, no wonders they were having trouble locating it, I dropped my database audit specification and SQL Server Audit specification.

USE AdventureWorks2012 ; 
GO 
ALTER SERVER AUDIT DataModification_Security_Audit WITH (STATE = OFF) ; 
GO
DROP DATABASE AUDIT SPECIFICATION Audit_Data_Modification_On_Table_Sales 
GO 

USE master ; 
GO 
ALTER SERVER AUDIT DataModification_Security_Audit WITH (STATE = OFF) ; 
GO
DROP SERVER AUDIT DataModification_Security_Audit -- make sure this path exists 
GO 

Finally, you will want to make sure to clean up the audit files from the server:

$ServerName = "YourServer"
# Change Directory as needed below
Invoke-Comannd -ComputerName $ServerName -ScriptBlock { Remove-Item -Path D:\SQLAudit\*.sqlaudit }