SQL Server：根据应用程序用户记录数据库更改

Question

当用户“123”登录到应用程序并对数据库中的数据执行更改时，我希望完成此更改将记录到具有“ 123“在额外的列。

想法是编写一个触发器，在镜像表中保存“插入”或“删除”，但问题是应用程序总是使用相同的数据库用户，所以我需要将应用程序用户与数据库连接不知何故。

所以我的想法是当用户登录到应用程序时，将应用程序用户的@@ SPID保存到表中。不幸的是，我发现@@ SPID对于每个查询都是不同的，即使我使用相同的ADOConnection类。

我也无法使用应用程序名称或主机名称，因为它可能是同一服务器上的不同用户启动相同的应用程序，我需要能够区分第一个用户所做的更改与第二个更改用户确实。

该应用程序非常大，因此更新每个查询以手动记录更改现在不是一个选项。

这个问题还有其他解决方案吗？

Answer 1

我现在实际上为它找到了一个解决方案：使用host_process_id。

基本上，当应用程序用户登录时，可以将他连接到dm_exec_sessions系统表中的host_process_id。然后，当触发器运行时，您可以从@@ SPID获取host_process_id以查明哪个用户执行了更改。

即使@@ SPID发生更改，host_process_id仍保持不变，但当同一应用程序在同一服务器上运行两次时，它的值不同。

Answer 2

最好的方法是捕获每个表中关于修改/插入/删除记录的用户信息。当用户对记录进行更改时，将他的详细信息（如ID）更新到“LastModifiedBy”列中。这应该解决捕获用户信息的问题.--因为我没有看到任何其他可能的方式，因此我们没有看到任何其他可能的方式。对审计日志记录的简单解决方案是在数据库表上启用SQL Change Data Capture。它将负责将审计信息记录到由其创建的系统表中。但是请注意，CDC有一个保留期限，通常是3天。因此，每三天数据将从CDC表格中删除。您可能希望探索禁用清理的机会，以便您可以将信息永久存储在CDC表中，或者只需编写.Net Windows服务或SSIS包以从CDC表中提取信息并推送到另一个审计数据库。