How to find out which host / IP address and Program deleted the records from SQL Server

Question

We are using SQL Server 2005. In our database some records are deleted. I want to know how these records are deleted like from which system (Host name / Ip address), program and date&time of deletion. can any one tell me how to find out?

Answer 1

You will not be able to find this information after the fact. You'll need to add a trigger and a logging table (or set up an expensive server-side trace, or add manual logging to your data access methods).

Here is a very quick example of how to implement a logging table and a trigger:

CREATE TABLE dbo.TableNameDeleteLog
( 
  PK_From_TableName INT,
  Program NVARCHAR(128),
  Host NVARCHAR(128),
  IP VARCHAR(48),
  When DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
GO

CREATE TRIGGER dbo.LogDelete_TableName
ON dbo.TableName
FOR DELETE
AS
BEGIN
  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.TableNameDeleteLog(PK_From_TableName, Program, Host, IP)
    SELECT PK_Column, @p, @h, @i
    FROM deleted;
END
GO

Answer 2

SQL Server does not store that information. If you have a backup, including transaction log files, you may be able to pinpoint when the deletion occurred, by restoring and searching for those missing records.

You can only find the hostname/IP/program provided you've logged that elsewhere. There may be traces in your eventlog, SQL log or other places, about the activity in general, but no specific logs (not to be confused with transaction log records) are stored for a record deletion.