This article discusses the ways to enable and use the SQL Server Audit option to audit the SQL database.
USE master;
GO
GRANT ALTER ANY SERVER AUDIT TO AuditConfigurationLogin
USE master;
GO
GRANT CONTROL SERVER TO AuditConfigurationLogin
In the above queries, master refers to the master database that records all the system-level information for a SQL Server system. The GO command is not a Transact-SQL statement. It is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.
USE WideWorldImporters;
GO
GRANT ALTER ANY DATABASE AUDIT TO AuditConfigurationLogin
USE WideWorldImporters;
GO GRANT ALTER TO AuditConfiguration;
USE WideWorldImporters;
GO
GRANT CONTROL TO AuditConfiguration;
The SQL Server Audit feature can be set up using either T-SQL, or SQL Server Management Studio options. Here are the steps to configure the SQL Server Audit using SQL Server Management Studio.
An audit destination can be a file (*.sqlaudit file), security log, or an application log. You don't require any permission to write to a file or an application log. However, the Audit Object Access setting must be configured to record the events into a security log. The best way to do this varies based on the operating system that you're using
You must have the generate security audits permission to write to the Windows Security log in the account that SQL Server is using. If the server is running under the LOCAL SERVICE and the NETWORK SERVICE accounts, they will have this permission by default.
To modify an existing audit you must first disable the audit. For this, right-click the audit and select the Disable Audit. The changes will not be saved if the existing audit isn't disabled before modifying.
Execute the following query in T-SQL to create and enable the server audit.
CREATE SERVER AUDIT [WideWorldImportersAudit_DDL_Access] TO FILE
( FILEPATH = N'D:\TestAudits\'
,MAXSIZE = 10 MB
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)
ALTER SERVER AUDIT [WideWorldImportersAudit_DDL_Access]WITH (STATE = ON)
GO
Once the audit object and database audit specifications have been enabled, every single entry in the table will be audited and reported.
You can use T-SQL and SQL Server Management Studio options to configure and enable the SQL Server Audit and its components. Compared to the auditing done using the SQL Server Change Tracking and Change Data Capture, this provides results at the granular level. The events are divided into groups and only individual groups are audited.
Interested in a
log management
solution?
Manage logs, comply with IT regulations, and mitigate security threats.
Our support technicians will get back to you at the earliest.
Zoho Corporation Pvt. Ltd. All rights reserved.