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
- @ReportforDays: Provide number of days for which audit report will be pulled. (Report for last 2 days or 20 days)
- @AuditFile: File Location and filename (Ex: D:\*abc*)
Store Procedure Code:
USE [MASTER] GO CREATE AS BEGIN SET 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 IF CREATE 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 INSERT 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 ——————-Statement—————————- INSERT SELECT event_time,session_server_principal_name,Statement WHERE session_server_principal_name !=” and event_time between ORDER 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 PRINT SET @StCountLoop=@StCountLoop+1 END ——————————————————— SET @CountLoop=@CountLoop+1 END SET END ————————————————————————————————————- |