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

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

Leave a Reply

Your email address will not be published. Required fields are marked *