Finding the user Activity in a specified Table Objects (Auditing)

 

 

In this article, I am creating and Trigger that track the user and the user activity related to specified table objects (INSERT and DELETE). You can easily turn it into good auditing scripts.

Please follow the steps to execute and understand the process of Auditing.

/*

   STEP-1

   Creating Test Table

*/

    CREATE TABLE TEST_TABLE

                 (ROLL  INT,

                  SNAME VARCHAR(50))

/*

    STEP-2

    Create the Table To trac Log

*/   

      CREATE TABLE [dbo].[TBL_TRAC_LOG]

                  (

                              ObjectName       sysname       NULL,

                              ObjectId         int           NULL,

                              SPID             smallint      NULL,

                              Status           nvarchar(30)  NULL,

                              Login            nvarchar(128) NULL,

                              Host             nvarchar(128) NULL,

                              BlkBy            smallint      NULL,

                              DBName           nvarchar(128) NULL,

                              CommandType      nvarchar(16)  NULL,

                              SQLStatement     nvarchar(max) NULL,

                              ElapsedMS        int           NULL,

                              CPUTime          int           NULL,

                              IOReads          bigint        NULL,

                              IOWrites         bigint        NULL,

                              LastWaitType     nvarchar(60)  NULL,

                              StartTime        datetime      NULL,

                              Protocol         nvarchar(40)  NULL,

                              ConnectionWrites int           NULL,

                              ConnectionReads  int           NULL,

                              ClientAddress    varchar(48)   NULL,

                              Authentication   nvarchar(40)  NULL

               )

 

/*   

    STEP-3

    You must Specify the table name Were U fix the Trigger 

*/

 

                                                 

IF EXISTS (SELECT *

           FROM sysobjects WHERE type = 'TR' AND name = 'TRG_MONITORING_INFO')

      BEGIN

            DROP  Trigger TRG_MONITORING_INFO

      END

GO

 

CREATE Trigger TRG_MONITORING_INFO ON TEST_TABLE

WITH ENCRYPTION

FOR INSERT, DELETE

AS

DECLARE @v_Param INT

BEGIN

       IF EXISTS(SELECT * FROM INSERTED)

          BEGIN

               SET @v_Param=1

          END

         

       IF EXISTS(SELECT * FROM DELETED)

          BEGIN

               SET @v_Param=0

          END  

 

       INSERT INTO TBL_TRAC_LOG

       SELECT    ObjectName       = sobj.name

                        ,ObjectId         = lock.rsc_objid

                        ,SPID             = er.session_id

                        ,[Status]         = ses.status

                        ,[Login]          = ses.login_name

                        ,Host             = ses.host_name

                        ,BlkBy            = er.blocking_session_id

                        ,DBName           = DB_Name(er.database_id)

                        ,CommandType      = CASE WHEN @v_Param=1 THEN 'INSERT' ELSE 'DELETE' END

                        ,SQLStatement     = st.text

                        ,ElapsedMS        = er.total_elapsed_time

                        ,CPUTime          = er.cpu_time

                        ,IOReads          = er.logical_reads + er.reads

                        ,IOWrites         = er.writes

                        ,LastWaitType     = er.last_wait_type

                        ,StartTime        = er.start_time

                        ,Protocol         = con.net_transport

                        ,ConnectionWrites = con.num_writes

                        ,ConnectionReads  = con.num_reads

                        ,ClientAddress    = con.client_net_address

                        ,[Authentication] = con.auth_scheme

            FROM    sys.dm_exec_requests er

                        OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st

                        LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id

                        LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id

                        LEFT JOIN (SELECT rsc_dbid, rsc_objid , req_spid FROM sys.syslockinfo)lock ON er.session_id=lock.req_spid

                        LEFT JOIN (SELECT name,id FROM sys.sysobjects)sobj ON lock.rsc_objid=sobj.id

            WHERE   sobj.name = 'TEST_TABLE'

            GROUP BY sobj.name

                        ,lock.rsc_objid

                        ,er.session_id

                        ,ses.status

                        ,ses.login_name

                        ,ses.host_name

                        ,er.blocking_session_id

                        ,er.command

                        ,st.text

                        ,er.total_elapsed_time

                        ,er.cpu_time

                        ,er.logical_reads + er.reads

                        ,er.writes

                        ,er.last_wait_type

                        ,er.start_time

                        ,con.net_transport

                        ,con.num_writes

                        ,con.num_reads

                        ,con.client_net_address

                        ,con.auth_scheme

                        ,er.database_id

                        ,st.objectid

 

 

                 

END

GO

 

/*

  STEP-4

  Testing Zone

*/

TRUNCATE TABLE TEST_TABLE

TRUNCATE TABLE TBL_TRAC_LOG

 

--Insert to Activate auditing

INSERT INTO TEST_TABLE

VALUES(1, 'JOYDEEP')

 

-- Final Output

SELECT * FROM TEST_TABLE

 

 

 

Hope the article is quite informative and thanking you for providing your valuable time on it.  

 

 

Posted by: MR. JOYDEEP DAS