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

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

————————————————————————————————————-

 

 

 

Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: