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 ONses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con ONcon.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 FROMsys.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.
