SQL Server数据库审核选择，失败的登录和整个数据库，所有对象的执行代码 [英] SQL Server database audit selects, failed logins and executed code for entire database, all objects

问题描述

我想跟踪所有失败到我们生产环境的登录.

I want to track all failed logins to our production environment.

包括所有对象的所有选择.

Including all selects to all objects.

基于:

https://www.simple-talk.com/sql/database-administration/sql-server-audit-magic-without-a-wizard/

和

https://www. simple-talk.com/sql/database-administration/sql-server-security-audit-basics/

尤其是:

https://blogs.msdn.microsoft.com/sreekarm/2009/01/05/auditing-select-statements-in-sql-server-2008/

这表明我需要在模式中命名每个对象，以便能够保存所有我不想执行的选择语句.有1500个表格和2300个视图.

It suggests I need to name each object, in the schema for me to be able to save all the select statements, which I don't want to do. There are 1500 tables, and 2300 views.

审计是否不可能获取数据库对象，并且对该对象执行的所有SELECT都保存在审计文件中，包括用户，语句和时间等?

Is it not possible for the audit, to take the database object, and any SELECT executed on that object is saved in the audit file, including user, statement and time etc.?

我从失败的登录主体组中获得了失败的登录，但是到目前为止，除非我特别指定要审核的对象，否则我无法获得select语句.

The failed login i get from the failed login principal group, but so far I've not been able to get the select statement, unless I specifically name the objects for which to audit.

命名它们，这也意味着我每次添加新视图或表时都必须更新审核.

Naming them, also means I have to update the audit every time a new view or table is added.

推荐答案

您可以使用扩展事件

对于您的特定情况，您可能需要选择batch starting和batch completed事件.

For your specific scenario,you might want to select batch starting and batch completed events..

您还可以在接下来的屏幕中添加更多信息，例如用户名，主机信息...

You can also add more info in the next screens like username,host info ...

最后，您可以添加过滤器以仅针对一个数据库或所有具有特殊名称和很多名称的proc过滤该数据库.

finally,you can add filters to filter this only for one database or all databases or proc with speficic name and a lot..

此信息可以记录到文件中以供以后分析.

This info can be logged to file for later analysis..

对于失败的登录，您可以右键单击服务器并转到下面的页面进行审核.默认情况下，此选项将启用，并记录到错误日志中

For Failed logins,you can right click server and go to below page to audit ..this will be enabled by default and it will be logged to error log

这篇关于SQL Server数据库审核选择，失败的登录和整个数据库，所有对象的执行代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！