4. Auditing Server Events
You create a server audit specification
to monitor events that occur at the server level, such as failed login
attempts or other actions not associated with any particular database.
As already described, you associate specifications to an audit object
configured for recording to either the file system or the event log.
Use the CREATE SERVER AUDIT SPECIFICATION statement to create a specification that monitors one or more server-level events for auditing. The FOR SERVER AUDIT clause links the specification with an audit object that defines the destination, and ADD clauses list the server-level audit action groups to be monitored. Similarly, the ALTER SERVER AUDIT SPECIFICATION statement can be used to ADD additional action groups to be monitored or DROP existing ones that should no longer be monitored.
Unlike audit objects, audit specifications can be created and enabled at the same time using CREATE SERVER AUDIT SPECIFICATION with the STATE=ONExample 5
create and enable a server audit specification that records all
successful logins and failed login attempts to the file system (to the
path C:\Demo\SqlAudits, as defined by the audit object MyFileAudit created earlier in the section). option. The statements in
Example 5. Creating a server audit specification.
CREATE SERVER AUDIT SPECIFICATION CaptureLoginsToFile
FOR SERVER AUDIT MyFileAudit
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP)
WITH (STATE=ON)
GO
After
executing this statement, all login attempts made against the server
(whether or not they succeed) will be audited to the file system. If
you later decide to also audit password changes and to stop auditing
successful logins, you can alter the specification accordingly (as with
audit objects, audit specifications must be disabled while they are
being changed), as shown in Example 6:
Example 6. Altering a server audit specification.
ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile WITH (STATE=OFF)
ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile
ADD (LOGIN_CHANGE_PASSWORD_GROUP),
DROP (SUCCESSFUL_LOGIN_GROUP)
ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile WITH (STATE=ON)
GO
You’ll find a complete list of server-level action groups that can be monitored for auditing in the SQL Server Audit Action Groups and Actions post at http://technet.microsoft.com/en-us/library/cc280663(v=sql.110).aspx.
There are more than 40 action groups, including backup and restore
operations, changes in database ownership, adding or removing logins
from server and database roles, or creating, altering, or dropping any
database object—just to name a few.
5. Auditing Database Events
A database audit specification
is conceptually similar to a server audit specification. Both specify
events to be monitored and directed to a designated audit object. The
primary difference is that database audit specifications are associated
with actions against a particular database, rather than server-level
actions.
Note
Database audit specifications reside in the database they are created for. You cannot audit database actions in tempdb.
The CREATE DATABASE AUDIT SPECIFICATION and ALTER DATABASE AUDIT SPECIFICATION statements work the same as their CREATE and ALTER
counterparts for server audit specifications that you just examined.
Like server audit specifications, database audit specifications can be
created in an enabled state by including the clause WITH (STATE=ON).
About
24 database-level action groups can be monitored for auditing, such as
changes in database ownership or permissions, for example. You can find
the complete list in the SQL Server Audit Action Groups and Actions post at http://technet.microsoft.com/en-us/library/cc280663(v=sql.110).aspx.
In addition, you can monitor for specific actions directly on database
objects, such as schemas, tables, views, stored procedures, and so on.
The seven database-level audit actions are SELECT, INSERT, UPDATE, DELETE, EXECUTE, RECEIVE, and REFERENCES.
For example, the code in Example 7 creates an event log audit object named MyEventLogAudit, a database named MyDB, and a database audit specification in the MyDB database named CaptureDbActionsToEventLog.
Example 7. Creating a database audit specification recorded to the event log.
-- Create an event log audit
USE master
GO
CREATE SERVER AUDIT MyEventLogAudit TO APPLICATION_LOG
ALTER SERVER AUDIT MyEventLogAudit WITH (STATE=ON)
-- Create a new database
IF EXISTS(SELECT name FROM sys.databases WHERE name = 'MyDB')
DROP DATABASE MyDB
GO
CREATE DATABASE MyDB
GO
USE MyDB
GO
-- Monitor database for all DML actions by all users to the event log
CREATE DATABASE AUDIT SPECIFICATION CaptureDbActionsToEventLog
FOR SERVER AUDIT MyEventLogAudit
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SELECT, INSERT, UPDATE, DELETE
ON SCHEMA::dbo
BY public)
WITH (STATE=ON)
The FOR SERVER AUDIT clause specifies that the monitored events should be directed to the server object MyEventLogAudit, which you created earlier to record audits to the application event log. The first ADD clause specifies DATABASE_OBJECT_CHANGE_GROUP, which watches for DDL changes made to any database object. This effectively audits any CREATE, ALTER, or DROP statement made against any object (table, view, and so on) in the database. The second ADD clause audits any DML action (SELECT, INSERT, UPDATE, or DELETE) made against any object in the dbo schema by any public user (which is every user).
Note
You get very fine-grained control with database audit specifications. The ON clause in the preceding statement causes every object in the dbo
schema to be audited, but it could just as easily be written to audit
DML operations on specific tables if desired. Similarly, rather than
auditing all users by specifying the public role in the BY clause, individual users and roles can be listed so that only DML operations made by those particular users are audited.
