Update the query and userid accessing a table

Question

Post reply

Update the query and userid accessing a table

sakthimadan

Old Hand

Points: 353
More actions
October 7, 2010 at 2:59 am

#229138

Hi all,
I need to log the user information(session id, User name) and the query run on a particular table.
I tried to do it by writing a trigger on the table to update the info into another table.
But its not working
Can anyone help me to sort out this issue..

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

What do you mean "accessing a table"?

There's no trigger for select, you would have to catch the event with a different strategy (trace or whatever).

If you want to log insert/update/delete, that's a different story.

Can you describe in detail what you're after?

-- Gianluca Sartori

sakthimadan Old Hand Points: 353 More actions · Answer 2

Sorry for the confusion,

I want to log the details of query and the user who are performing DML operations like INSERT/UPDATE/DELETE.

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

This could be a template for your trigger:

-- =============================================

-- Author:Gianluca Sartori

-- Create date: 10/07/2010

-- Description:Catches SQL DML statements and logs

-- to a log table

-- =============================================

CREATE TRIGGER TR_LogDML

ON MyTable

FOR INSERT,DELETE,UPDATE

AS

BEGIN

SET NOCOUNT ON;

DECLARE @SQLBuffer nvarchar(4000)

DECLARE @program_name nvarchar(128)

DECLARE @host_name nvarchar(128)

DECLARE @user_name varchar(30)

DECLARE @session_id int

SELECT @program_name = program_name,

@host_name = host_name,

@user_name = USER(),

@session_id = @@SPID

FROM sys.dm_exec_requests AS a

INNER JOIN sys.dm_exec_sessions AS c

ON a.session_id = c.session_id

WHERE a.session_id = @@spid

DECLARE @buffer TABLE (

EventType nvarchar(30),

Parameters int,

EventInfo nvarchar(4000)

)

INSERT @buffer

EXEC sp_executesql N'DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS'

SELECT @SQLBuffer = EventInfo

FROM @buffer

INSERT INTO MyLogTable (

[Session_id], [User_Name], [Host_Name], [Proram_Name], [SQL_Text]

)

SELECT @session_id

,@user_name

,@host_name

,@program_name

,@SQLBuffer

END

Hope this helps,

Gianluca

-- Gianluca Sartori

sakthimadan Old Hand Points: 353 More actions · Answer 4

Hi Gianluca,

Its perfectly logging what i want.

Thanks a lot....

Regards,

Sakthimadan

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

You're welcome.

Glad I could help.

-- Gianluca Sartori