How to capture Delete and Truncate T-SQL using Database Audit Specification

It really becomes evident to capture Delete and Truncate T-SQL in an environment which is supported by a Heterogeneous Environment. It really helped me to understand the scenario and pattern when DBA/ Jobs/SSIS Packages deletes or truncate Data from a Table based on Application / Business requirement.

Here we will see how to configure Database Audit Specification to capture delete and Truncate T-SQL .Later we will see how we can view the audit report and send the same in an Email.

Connect to the SQL Server Instance and navigate to SecurityàAuditàNew Audit

Provide the Audit Name (Filename) Audit Destination (File) and File Path (x:\).

Audit Configuration first created is Disable (Enable the same)

Select the Database on which delete and Truncate T-SQL to be captured.

DatabaseàSecurityàDatabase Audit Specification

Type a Name for the Database Audit and select the Audit name which was configured to capture the T-SQL Commands.

In “Audit Action Type” Select the Delete and “Object Class” as Database and Object Name will be the Database Name. Select the Principal name as Schema Name by which objects where created.

Truncate Commands are translated as Delete in the Audit file. Enable the Database Audit Speciation once configuration is complete.

Now execute delete and truncate commands against the database on which Audit is configured. Once commands are executed and completed check the commands are captured and written in an Audit File.

SELECT event_time,succeeded,object_id,object_name,session_server_principal_name,server_principal_name,server_instance_name,database_name,Statement,file_name,audit_file_offset from fn_get_audit_file (‘D:\*–SM*’,null,null) order by event_time desc

 

To simplify this and schedule the same in a report I have created Stored Procedure which gives the output as UseràCommand Execution DateàNo of Commands Executed

The stored procedure has two parameters

1. @ReportforDays: Provide number of days for which audit report will be pulled. (Report for last 2 days or 20 days)
   
2. @AuditFile: File Location and filename (Ex: D:\*abc*)
   

Store Procedure Code:

USE [MASTER] GO CREATE PROCEDURE dba_audit_varify (@ReportforDays VARCHAR(2),@AuditFile VARCHAR(MAX)) AS BEGIN SET NOCOUNT ON DECLARE @ReportDays VARCHAR(2) DECLARE @StartLoop INT DECLARE @CountLoop INT DECLARE @StStartLoop INT DECLARE @StCountLoop INT DECLARE @Exe_User     VARCHAR(255) DECLARE @ex_dt                 DATETIME DECLARE @Statements VARCHAR(MAX) DECLARE @AuditFilename         VARCHAR(MAX) DECLARE @Executor             TABLE (ex_id INT IDENTITY (1,1),ex_user VARCHAR(255) ) IF EXISTS (SELECT TOP 1 * FROM tempdb.sys.sysobjects where name like ‘%#Executor_statement%’) DROP TABLE #Executor_statement CREATE TABLE #Executor_statement (ex_id INT IDENTITY (1,1),ex_user VARCHAR(255),Statements VARCHAR(MAX),execution_dt DATETIME ) DECLARE @session_server_principal_name_text varchar(MAX) SET @ReportDays=@ReportforDays SET @AuditFilename=@AuditFile                 SET @session_server_principal_name_text=‘SELECT DISTINCT session_server_principal_name FROM fn_get_audit_file                 (‘+””+@AuditFilename+”’,null,null) WHERE session_server_principal_name !=””’+‘ and event_time between GETDATE() ‘+ ‘- ‘ +@ReportDays +‘ and GETDATE()’                 Print space(5) + ‘[-] User Audit Report for Database ‘                           INSERT INTO @Executor (ex_user)         EXEC (@session_server_principal_name_text)         SET @StartLoop=@@ROWCOUNT         SET @CountLoop=1             While @CountLoop <= @StartLoop                     BEGIN                     SELECT @Exe_User= ex_user from @Executor WHERE ex_id=@CountLoop                     –PRINT space(11)+’|’                     PRINT space(5)+‘[-]’+ ‘ User :[‘ + @Exe_User + ‘] ‘                                          ——————-Statement—————————-                         INSERT INTO #Executor_statement (execution_dt,ex_user,Statements)                         SELECT event_time,session_server_principal_name,Statement from fn_get_audit_file    (@AuditFilename,null,null)                         WHERE session_server_principal_name !=” and event_time between GETDATE() –convert(int,@ReportDays) and GETDATE() and session_server_principal_name=@Exe_User                         ORDER BY event_time desc                             SET @StStartLoop=@@ROWCOUNT                             SET @StCountLoop=1                             –Print @StCountLoop                             While @StCountLoop <= @StStartLoop                                 BEGIN                                                     SELECT @ex_dt=execution_dt,@Statements=Statements from #Executor_statement WHERE ex_id=@StCountLoop                                     PRINT space(6)+‘|’+space(5)+‘[|-]’+‘Execution_Date :[‘ + convert(varchar(20),@ex_dt) + ‘]’                                     PRINT space(6)+‘|’+space(5)+‘[|-]’+‘Query :’+@Statements                                     SET @StCountLoop=@StCountLoop+1                                 END                     ———————————————————                     SET @CountLoop=@CountLoop+1                     END SET NOCOUNT ON END ————————————————————————————————————-