Sql-server – Find the identity of the client firing a query in SQL Server without using triggers

auditchange-data-capturesql serversql-server-2012

I'm currently using Change Data Capture (CDC) to track data changes, and I wish to track the host name and IP address of the client submitting the query that made the changes. If there are 5 different clients logged in via the same user name, one faces the conundrum of tracking which of the 5 fired the query. Other specious solutions that I found include altering the CDC table with following command:

ALTER TABLE cdc.schema_table_CT 
ADD HostName nvarchar(50) NULL DEFAULT(HOST_NAME())

However, this returns the host-name of the server on which the query was fired, and not the host-name of the client that fires the query.

Is there a way around this problem? Something that would help to log the host-name or the IP address (or some other kind of unique identity) of the client.
I do not want to use triggers, as it slows down the system, also CDC generates system tables, so having a trigger on that is apparently not possible.

Best Answer

I am not sure about CDC, but if the login has view server state permission you can use DMVs to get some information.

This is given in Books Online here. I changed the query to add columns which would give you the IP address:

SELECT 
    c.session_id, c.net_transport, c.encrypt_option, c.auth_scheme,
    s.host_name, s.program_name, s.client_interface_name,
    c.local_net_address, c.client_net_address, s.login_name, s.nt_domain, 
    s.nt_user_name, s.original_login_name, c.connect_time, s.login_time 
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id
WHERE c.session_id = SPID;  --session ID you want to track

Related Solutions

Sql-server – SQL Server Database Mirroring : strange client application behavior upon failover

Suggest you check to see that each box has each FQDN registered with port, etc, for each target/ sql-srv, as well as the witness FQDN. Each server has all three registered with FQDN. Reinitialized the endpoints and cross your fingers ! :) If in DMZ, are you able to use AD inside DMZ ? or maybe there is trust back out the corp AD ? If short names registered and FQDN, think this trust might be giving you fits, so presented with non-FQDN being used. Rick

Sql-server – How to retrieve the Capture Instance for a given table

Input (hopefully stored procedure parameters):

DECLARE @table1 NVARCHAR(513) = N'dbo.Users',
        @table2 NVARCHAR(513) = N'dbo.DataObjects',
        @ID     INT = 10;

Code:

DECLARE @InstanceName1 NVARCHAR(513),
        @InstanceName2 NVARCHAR(513),
        @Begin_LSN     BINARY(10),
        @End_LSN       BINARY(10);

SELECT @InstanceName1 = c.capture_instance
  FROM cdc.change_tables AS c
  INNER JOIN sys.tables AS t
  ON c.[source_object_id] = t.[object_id]
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE t.name = PARSENAME(@table1,1)
    AND s.name = PARSENAME(@table1,2);

SELECT @InstanceName2 = c.capture_instance
  FROM cdc.change_tables AS c
  INNER JOIN sys.tables AS t
  ON c.[source_object_id] = t.[object_id]
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE t.name = PARSENAME(@table2,1)
    AND s.name = PARSENAME(@table2,2);

SELECT @Begin_LSN = sys.fn_cdc_get_min_lsn(@InstanceName1), 
       @End_LSN   = sys.fn_cdc_get_max_lsn();

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'SELECT * 
  FROM cdc.fn_cdc_get_all_changes_' + @InstanceName2
  + '(@b, @l, ''ALL'') AS a 
  INNER JOIN cdc.fn_cdc_get_all_changes_' + @InstanceName1
  + '(@b, @l, ''ALL'') AS b 
  ON a.__$start_lsn = b.__$start_lsn
  WHERE a.ID = @ID;';
  ------^ guessing here

EXEC sp_executesql @sql, 
  N'@b BINARY(10), @l BINARY(10), @ID INT',
  @Begin_LSN, @End_LSN, @ID;

Best Answer

Related Solutions

Sql-server – SQL Server Database Mirroring : strange client application behavior upon failover

Sql-server – How to retrieve the Capture Instance for a given table

Related Question