Audit specifications can have three categories of actions:
This article discusses how server level actions and database level actions are enabled for auditing on an SQL server using audit specification.
These are the audit specifications for actions carried out at the server level. It is available in all the editions of SQL server. There can be only one server audit specification per audit.
To create server audit specification, the user needs :
Steps to create Server Audit Specification:
These are the audit specifications for actions carried out at the database level. Database audit specification is available only in the Enterprise edition of SQL server and can be carried out at the object or user level.
To create server audit specification, you need the below permissions:
Steps to create database audit specification:
The following needs to be configured to create a Database Audit Specification:
Here's a scenario in which we want to audit the changes made to an employee pay history table. Assume that the employee details are stored in HumanResource.EmployeePayHistory table. First, we define a server audit called Payrole_Security_Auditc and enable it. We then proceed to create a database audit specification called Audit_Pay_Tables. This specification audits SELECT and INSERT statements by the dbo user for the table, as required.
Steps to enable auditing for the above scenario
USE master ;
-- Create the server audit.
CREATE SERVER AUDIT Payrole_Security_Audit
TO FILE ( FILEPATH =
'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA' ) ;
-- Enable the server audit.
ALTER SERVER AUDIT Payrole_Security_Audit
WITH (STATE = ON) ;
USE AdventureWorks2012 ;
-- Create the database audit specification.
CREATE DATABASE AUDIT SPECIFICATION Audit_Pay_Tables
FOR SERVER AUDIT Payrole_Security_Audit
ADD (SELECT , INSERT
ON HumanResources.EmployeePayHistory BY dbo )
WITH (STATE = ON) ;
Auditing procedures such as these involve a lot of time and human effort. This is as far as the auditing goes—you still don't get a holistic picture on the security of your SQL database.
ManageEngine's EventLog Analyzer, a comprehensive log management tool, audits both SQL server and SQL database. It provides out-of-box reports, real-time alerts and even an intuitive dashboard You can drill down to the logs, filter reports, customize alerts, perform log searches, and archive logs for powerful and effective management of SQL Servers. Click here to know to more
Zoho Corporation Pvt. Ltd. All rights reserved.