Determine the origin IP address of queries against a table?

I have a table that is being updated by a service being run at an unknown location.

I've checked all of the servers that normally communicate with my database server and none of them appear to have the service in question running.

I am trying to track down where this service is running, and I'm wondering if it is possible for me to see where queries that affect a particular table originate from?

Is this possible?

Simplest method is to use a trigger and log the IP address from sys.dm_exec_connections. You'll need a logging table; I've guessed at a couple of columns you'll want, but you may want others.

CREATE TABLE dbo.LoggingTable
(
    LoggingID INT NOT NULL
        CONSTRAINT PK_LoggingTable
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , AppName SYSNAME
    , HostName SYSNAME
    , ClientNetAddress VARCHAR(255)
    , LogDateTime DATETIME
);
GO

CREATE TRIGGER dbo.triggername
ON dbo.tablename /* name of the target table  */
FOR INSERT, UPDATE, DELETE
AS
BEGIN
  INSERT dbo.LoggingTable(AppName, HostName, ClientNetAddress, LogDateTime) 
  SELECT app_name(), host_name(), client_net_address, SYSDATETIME()
  FROM sys.dm_exec_connections dec
  WHERE dec.session_id = @@SPID;
END
GO

Note that if the update is coming from a connection on the same machine ("The call is coming from inside the house!"), and using shared memory, you'll get ` as the IP address.

Once you've figured it out, don't forget to drop the trigger.

More information about the columns in sys.dm_exec_connections`

StackExchange Database Administrators Q#118304, answer score: 9