Unraveling The Power of SQL Server Audit Options and Specifications: Question and Answer

Q.1. What does The MAXSIZE Option do?

Ans: The MAXSIZE option specifies the maximum size that an audit file can reach before it's closed and a new file is opened, which is called "rolling over." The size is specified as an integer followed by MB, GB, or TB for megabytes, gigabytes, or terabytes, respectively. It's important to note that the minimum value allowed is one megabyte. Additionally, MAXSIZE can be set to UNLIMITED, which is the default value, allowing the audit file to grow to any size before rolling over.

Q.2. Explain The purpose of The MAX_ROLLOVER_FILES Option.

Ans: The MAX ROLLOVER FILES option helps manage the file system automatically as auditing data accumulates over time. By default, this option is set to UNLIMITED, meaning that no cleanup is performed when new audit files are created. This could eventually fill up the disk. Alternatively, you can specify an integer value for this option, indicating how many audit files are retained in the file system as they roll over, while older audit files are automatically deleted.

Q.3. What is The significance of The MAX_FILES Option?

Ans: The MAX FILES option, introduced in SQL Server 2012, requires manual file system grooming. When the specified number of files is created, SQL Server will start generating errors for any audited action. It won't roll over or delete old auditing files as they accumulate over time. This option is useful if you want to ensure that audit files are managed manually, and SQL Server never deletes audit files automatically.

Q.4. What does The RESERVE_DISK_SPACE Option do?

Ans: The RESERVE DISK SPACE option is OFF by default, which means that disk space is dynamically allocated for the audit file as it grows to record more events. However, preallocating disk space for the audit file when it's created can improve performance and reduce disk fragmentation. When you set this option to ON, the audit file will be allocated the amount of space specified by the MAXSIZE option. To use RESERVE DISK SPACE=ON, MAXSIZE must be set to a value other than the default UNLIMITED.

Q.5. How can Audits be Recorded to The Windows Event Log?

Ans: You can create and save audit objects to the Windows event log using the "TO APPLICATION_LOG" or "TO SECURITY_LOG" options. The former sends audit entries to the Application event log, while the latter sends them to the Security event log. This allows you to track specific events in these logs. An example of creating an audit object recorded in the Application event log will be demonstrated in an upcoming sample.

Q.6. Define The Server Audit Specification.

Ans: A server audit specification is created to monitor server-level events, such as failed login attempts or other actions not associated with any specific database. As mentioned earlier, specifications are linked to an audit object configured to record to either the file system or the event log.

Q.7. How do you Create a Server Audit Specification?

Ans: To create a specification that monitors server-level events for auditing, use the "CREATE SERVER AUDIT SPECIFICATION" statement. The "FOR SERVER AUDIT" clause associates the specification with an audit object that defines the destination. The "ADD" clauses specify the server-level audit action groups that will be monitored. You can also use the "ALTER SERVER AUDIT SPECIFICATION" statement to add new action groups to be monitored or drop ones that should not be monitored. Audit specifications can be created and enabled simultaneously by using the "CREATE SERVER AUDIT SPECIFICATION" with the "STATE=ON" option.

Q.8. What is a Database Audit Specification?

Ans: A database audit specification is similar in concept to a server audit specification. Both specify events to be tracked and directed to a specific audit object. However, the key difference is that database audit specifications are associated with actions against a specific database, as opposed to server-level actions. These specifications are stored in the database for which they were created, and they can't audit actions in tempdb.

Q.9. How can you Create a Database Audit Specification Recorded to The Event Log?

Ans: Database audit specifications, like server audit specifications, can be created with the "WITH (STATE=ON)" clause. The "CREATE DATABASE AUDIT SPECIFICATION" and "ALTER DATABASE AUDIT SPECIFICATION" statements function similarly to their counterparts for server audit specifications. Around 24 database-level action groups can be monitored for auditing, including changes in database ownership or permissions, and specific actions on database objects like schemas, tables, views, stored procedures, etc. The "SELECT, INSERT, UPDATE, DELETE, EXECUTE, RECEIVE, and REFERENCES" actions are the seven database-level audit actions. You can create 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)

Conclusion

SQL Server audit options and specifications provide essential tools for managing and securing your database environment. The MAXSIZE option allows you to control audit file sizes, while MAX_ROLLOVER_FILES and MAX_FILES help automate file system cleanup. The RESERVE_DISK_SPACE option can enhance performance by preallocating space. Server audit specifications enable monitoring of server-level events, and database audit specifications provide granular control over database-level actions. By understanding and effectively utilizing these options, you can enhance security, optimize performance, and maintain a well-organized database system.