Search

Monday, December 10, 2012

Find out which host / IP address and Program deleted the records from SQL Server

You will not be able to know from which system records are deleted directly. But you can get this information if you add a trigger and a logging table. Use below query to implement a logging table and a trigger:
CREATE TABLE dbo.DeleteLogTable  PK_From_Table INT,  Host NVARCHAR(128),  IP VARCHAR(48),  Program NVARCHAR(128),  Deleted_When DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP);GO

CREATE TRIGGER dbo.LogDelete_TableON dbo.TableNameFOR DELETEASBEGIN  SET NOCOUNT ON;  DECLARE @p NVARCHAR(128), @h NVARCHAR(128), @i VARCHAR(48);  SELECT @p = s.host_name, @h = host_name, @i = c.client_net_address    FROM sys.dm_exec_sessions AS s    INNER JOIN sys.dm_exec_connections AS c    ON s.session_id = c.session_id    WHERE s.session_id = @@SPID;  INSERT dbo.DeleteLogTable(PK_From_Table, Program, Host, IP)    SELECT PK_Column, @p, @h, @i    FROM deleted;END

GO

No comments:

Post a Comment