SQL Server 2008 T-SQL command for audit SELECT statements

Source: Internet
Author: User
Tags file system create database

The SQL audit in SQL Server 2008 has become a first-class server object. As mentioned in the previous article, you can now even audit a SELECT statement query that requires the use of SQL tracing or other Third-party products in previous versions. However, although using a graphical user interface is good for dealing with small tasks, especially when learning something for the first time, doing so may be tedious, especially if you repeat the same task. This article will use the same example as the previous article through similar Transact-SQL statements. If you want to audit, we can assume that audit in the database 10 or more tables, then this will make it easier for you to do. I will also use the file system to store audit objects. We will use the Create SERVER audit to complete this task.

-- Create Server Audit object
USE master

This server audit object collects the server and/or database-level behavior and behavior groups you want to monitor and creates at the instance level. This also means that if you have many instances running on a single server, you will create a server audit object for each instance. Next, we will create a database audit specification called TESTAUDSPEC in the appropriate database, and in this case we will use the AdventureWorks database. We will use the Create DATABASE AUDIT specification statement to complete this task.

-- Create an audit specification
USE AdventureWorks
ADD (SELECT ON HumanResources.Employee BY Public)

What we're doing here is just creating a database audit specification and assigning it to the server audit object we created earlier. We then add the database-level auditable behavior name, which in this case is a SELECT statement, and then captures the queries executed by all users. You can have multiple behavioral or behavioral groups and related database audit specifications. You can also create a server audit specification that can audit server-level behavior or behavior groups, such as tracking successful or failed logins, changing role members, and so on. A list of SQL Server audit behavior groups and behaviors can be obtained from online Help and MSCN.

Because the server auditing object and the database auditing specification are not available by default, we need to make them usable. To verify their status, you can query sys.server_file_audits and

Sys.database_audit_specifications the System object. The is_state_enabled field can tell us their state.

-- Verify Audit objects if turned on (1 = Enabled)
USE master
SELECT is_state_enabled,* FROM sys.server_file_audits
USE AdventureWorks
SELECT is_state_enabled,* FROM sys.database_audit_specifications

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.