SQL Server是否保留活动日志？ [英] Does SQL Server keeps Activity Log?

问题描述

Hello Everyone;



我在SQLServer中有一个数据库，并且想要一个审计日志文件，该文件可以显示自创建以来该数据库上执行的每个操作。这是为了让我知道是否有人删除了一条记录或直接使用SQL服务器进行了修改，而不是通过数据库上的应用程序进行修改。



SQLServer是否具备该功能？

Hello Everyone;

I have a db in SQLServer and want an audit log file that could reveal every single operation perfomed on that database since it was created. This is to let me know if someone has deleted a record or modified directly using SQL server and not via application sitting on the database.

Does SQLServer has that facility?

推荐答案

SQL Server有一个活动监视器，可以识别哪个应用程序打开了一个会话...请参阅 http://msdn.microsoft.com/en-us/library/ms175518.aspx [ ^ ]

SQL Server has an Activity Monitor that can identify which application opened a session ... see http://msdn.microsoft.com/en-us/library/ms175518.aspx[^]

内置SQL Server审核 [ ^ ]（另见：http://www.dbsimplified.com/2011/10/auditing-in-sql-server。 html [ ^ ]）来自v2008是一个非常有用的工具，但仅限于Enterprise，Developer和Evaluation版本（ http://msdn.microsoft.com/en-us/library/cc280386.aspx [ ^ ]）。现在，在这里您可以比较您的可能性： http://www.slideshare.net/sqlserver.co.il/a-comparative-analysis-of-auditing-solutions-in-sql-server [ ^ ]。

如果你有其他版本或更旧的服务器版本，你可以使用跟踪方法： http://sqlmag.com/sql-server-2005/get-compliant-sql-server-2005-audit-logging [ ^ ]。

您还可以使用 SQL Server扩展事件。 [ ^ ]。

当然你可以使用dml触发器，但这也有它的局限性，请参阅： http://sql-box.com/2011/07/10/track-dml-changes-using-after-trigger-for-update-delete-and-insert-rows-capture-changed-data-rows-using -t-sql / [ ^ ]和快速SQL Server审核设置 [ ^ ]

The Built-in SQL Server Auditing[^] (see also: http://www.dbsimplified.com/2011/10/auditing-in-sql-server.html[^]) from v2008 is a really useful tool, but it is limited to Enterprise, Developer, and Evaluation editions (http://msdn.microsoft.com/en-us/library/cc280386.aspx[^]). Now, here you have a comparison of your possibilities: http://www.slideshare.net/sqlserver.co.il/a-comparative-analysis-of-auditing-solutions-in-sql-server[^].
If you have an other edition, or older server version, you can use tracing approach: http://sqlmag.com/sql-server-2005/get-compliant-sql-server-2005-audit-logging[^].
You could also make use of the SQL Server Extended Events.[^].
And of course you can make use of dml triggers, but that has it''s limitations also, see: http://sql-box.com/2011/07/10/track-dml-changes-using-after-trigger-for-update-delete-and-insert-rows-capture-changed-data-rows-using-t-sql/[^] and Quick SQL Server Auditing Setup[^]

这篇关于SQL Server是否保留活动日志？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！