How to find out which host / IP address / program performed a delete?

We are using SQL Server 2005. In our database some rows are deleted; how can I find the system (host name / IP address), program and date & time of deletion?

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

StackExchange Database Administrators Q#21879, answer score: 5