February 8, 2008 at 6:12 am
Hi All
I have a problem in detecting a record deletion on a particular table, i cant use the trace since its only one record is been deleted for a whole day, i need to find which user or application is doing this, i can use a delete trigger on the table where the user is been deleted, but i want to know how shall i get information of the user and machine who is trying to delete the record( i am using SQL 2005), can anyone please help me on this
Cheers
Sujith
February 8, 2008 at 6:57 am
You can use profiler and put filter on perticular Object on which you would like to monitor activity.
Hope this will help.
Cheers!!!
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
February 8, 2008 at 7:08 am
Hi Mascot
I cant use Profiler due to space restrictions and deletion is only be done once a day and i cant figure out the Approximate time,
Cheers
Sujith
February 8, 2008 at 7:26 am
Suji,
You can use the System_User and HOSTNAME() function to return the logged in user and his host computer.
Your trigger should look something like this:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER dbo.MyDeleteTriggerName
ON dbo.MyTable
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
INSERT INTO MyAuditTable
SELECT Col1,Col2,Col3, SYSTEM_USER,HOST_NAME()
FROM DELETED
END
GO
Note this trigger only fires for deleted rows, if a value if modified in a row the trigger is not fired. For this type of tracking you would need the after delete, update
February 8, 2008 at 8:27 am
Thanks Adam, it really helped me,
Cheers
Sujith
February 8, 2008 at 8:29 am
you can call system functions in the trigger (suser_sname(), user user_name()) to find out which account is used.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply