SQL Server Change Data Capture - 捕获进行更改的用户

2021-11-30 00:00:00 sql-server audit sql-server-2014 change-data-capture

关于SQL Server 更改数据捕获，您能否跟踪对行/列数据进行更改的用户，或者是否可以扩展 CDC 以允许这样做?我在文档中看不到任何内容.

Concerning SQL Server Change Data Capture, can you track the User who has made the change to the row/column data or is there anyway to extend CDC to allow this? I couldn't see anything in the documentation.

推荐答案

您无法使用 CDC 获取用户名..

You can't capture username with CDC..

您必须使用审计来执行此操作，或者如果这是一次性请求，您可以查询 TLOG..

You have to use Auditing to do so or if this is a one time request,you can query TLOG..

下面是请求相同的连接项..

Below is the connect item requesting the same..

CDC :用于捕获更多数据(用户名、日期/时间等)的选项

您也可以按照本文使用触发器玩卡特迈的疾病预防控制中心来自 Aaron Bertrand..

You also can use triggers as per this article Playing with CDC in Katmai from Aaron Bertrand..

创建表格:

CREATE TABLE cdc.dbo_test_CT_MoreInfo
(
startlsn BINARY(10),
seqval BINARY(10),
operation INT,
username SYSNAME NOT NULL DEFAULT SUSER_SNAME(),
eventDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (startlsn, seqval, operation)
);
GO

创建触发器:

CREATE TRIGGER cdc.LogMoreCDCInfo
ON cdc.dbo_test_CT
FOR INSERT
AS
BEGIN
  IF @@ROWCOUNT > 0
  BEGIN
    INSERT cdc.dbo_test_CT_MoreInfo(startlsn,seqval,operation)
      SELECT __$start_lsn, __$seqval, __$operation FROM inserted;
  END
END
GO

相关文章