如何在Sql Server中实现审核日志
我们有一个 (asp.net) 应用程序，它使用专门为此应用程序设置的登录凭据连接到 SQL Server 2008 R2 数据库。
我们的应用程序通过存储过程访问/修改/删除记录，我们将执行操作的用户的用户名作为参数传递给存储过程。
我们需要能够保留某些表上所有更新和删除的审核日志。 该解决方案还需要尽可能少，并且不需要任何开发人员干预。
我能找到的最简单的方法是在表（A）上放置一个触发器，将“旧”数据复制到历史表（A_History）中 问题是我们需要能够知道谁执行了该操作，对于更新来说这很容易，我们只需查看新插入记录的用户名即可。但是对于删除，我们无权访问发送到存储过程的用户名参数。
有没有办法在不使用 CONTEXT_INFO() 之类的情况下实现这一点，因为这需要添加到每个存储过程和每个调用中，这肯定会被忘记。
We have an (asp.net) application that connects to a SQL Server 2008 R2 database using login credentials specifically setup for this application.
Our application access/modifies/deletes records via stored procedures, to which we pass the username of the user performing the action as a parameter.
We need to be able to keep an audit log of all updates and deletes on certain tables.
The solution also needs to be as minimal as possible and not require any developer intervention.
The easiest way I can find, is to put a trigger on the table (A) that copies the 'old' data into a history table (A_History)
The problem is that we need to be able to tell who performed the action, for an update this is easy we just look at username of the newly inserted record. But for a delete we do not have access to the username parameter sent to the stored procedure.
Is there any way to implement this without using things like CONTEXT_INFO(), because this would need to be added to every stored procedure and every call, which is bound to be forgotten.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
SQL 更改跟踪可能对您有帮助。
现在，您可能应该做一些其他事情：
记录。
柱子。
按用户 ID 修改和按用户 ID 创建
列。
或者以其他方式更新
ModifiedByUserID 列
呼叫者的用户 ID。
实现所有这些，SQL 将跟踪这些记录的每个版本...谁创建了它们...谁修改了它们...谁将它们标记为已删除。
SQL Change Tracking may be helpful to you.
Now, you should probably do a few other things:
records.
column.
ModifiedByUserID and CreatedByUserID
columns.
or otherwise, update the
ModifiedByUserID column with the
caller's user id.
Implement all that and SQL will keep track of every version of those records... who created them... who modified them... who marked them as deleted.