How to Setup SQL Server Audit using SQL scripts

Navigating the complexities of SQL Server auditing can be daunting. Whether you’re ensuring compliance, securing data, or simply looking to understand changes within your database environment, auditing is critical. This blog is dedicated to uncovering the intricacies of SQL Server Audit, a powerful feature that helps you monitor and record server activity to meet compliance and security requirements.

Here, you’ll find detailed articles, how-to guides, and expert tips on setting up, configuring, and managing SQL Server audits. We’ll explore topics like audit configuration, event monitoring, and analysis to help you effectively track and respond to changes across your SQL Server instances.

Join us to deepen your knowledge of SQL Server auditing, enhance your security strategies, and ensure your database environments are compliant and well-managed. Whether you’re a seasoned DBA or new to database security, our blog aims to provide you with the resources you need to master SQL Server auditing.

To set up SQL Server Audit using SQL scripts

You can use the following scripts tailored to create a server audit and audit specification, enabling you to track changes in security settings. These scripts will guide you through creating an audit, defining what to audit, and turning on the audit.

Step 1: Create a Server Audit

This script creates a server audit object that specifies where the audit logs will be stored. Modify the file path and other parameters as needed.

-- Create a Server Audit
USE master;
GO

CREATE SERVER AUDIT [SecurityAudit]
TO FILE 
(
    FILEPATH = 'D:\Temp\SQLServer\Audits\', 
    MAXSIZE = 100 MB,
    MAX_FILES = 10,
    RESERVE_DISK_SPACE = OFF
)
WITH
(
    QUEUE_DELAY = 1000,  -- Milliseconds
    ON_FAILURE = CONTINUE
);
GO

Step 2: Create a Server Audit Specification

This script sets up a server audit specification that details the types of security-related actions to audit. You can add or remove actions based on what you need to track.

-- Create a Server Audit Specification for security-related events
USE master;
GO

CREATE SERVER AUDIT SPECIFICATION [SecurityAuditSpec]
FOR SERVER AUDIT [SecurityAudit]
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (SERVER_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_PERMISSION_CHANGE_GROUP),
ADD (LOGIN_CHANGE_PASSWORD_GROUP),
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP)
WITH (STATE = OFF);
GO

Step 3: Enable the Audit and Audit Specification

Once the audit and audit specification are created, you need to enable them.

-- Enable the Server Audit
ALTER SERVER AUDIT [SecurityAudit] WITH (STATE = ON);
GO

-- Enable the Server Audit Specification
ALTER SERVER AUDIT SPECIFICATION [SecurityAuditSpec] WITH (STATE = ON);
GO

Additional Notes

• Make sure the directory path for the audit file (D:\Temp\SQLServer\Audits\ in the example) exists on the server and SQL Server has adequate permissions to write to that location.
• Modify the MAXSIZE, MAX_FILES, and other parameters as per your disk capacity and auditing requirements.
• Review SQL Server’s documentation on audit action groups to better understand which actions are covered by each group and adjust the audit specification according to your needs.
• Always test the audit configuration in a development environment before deploying it to production to ensure that it captures the right events and does not adversely impact the performance.

By running these scripts, SQL Server will start auditing the specified events, and you can view the audit logs through SSMS or query them directly for analysis and monitoring purposes.

Testing SQL Server Audit Setup

To effectively test your SQL Server Audit setup, perform actions that trigger the audit events you’ve set up to monitor. Below are some example SQL scripts to test various audit events that you might have included in your server audit specification. These scripts will help verify if the audit is correctly capturing the intended events.

Testing Script 1: Change Server Role Membership

This script adds a user to a server role, which should trigger the SERVER_ROLE_MEMBER_CHANGE_GROUP event.

-- Add a login to the 'sysadmin' server role
USE master;
GO

ALTER SERVER ROLE sysadmin ADD MEMBER [YourLoginName];
GO

Testing Script 2: Change Database Role Membership

This script adds a user to a database role, triggering the DATABASE_ROLE_MEMBER_CHANGE_GROUP event.

-- Add a user to the 'db_owner' database role
USE YourDatabaseName;
GO

ALTER ROLE db_owner ADD MEMBER [YourUserName];
GO

Testing Script 3: Change Server Permissions

This script grants a server-level permission, which should capture an event under SERVER_PERMISSION_CHANGE_GROUP.

-- Grant 'VIEW SERVER STATE' permission
USE master;
GO

GRANT VIEW SERVER STATE TO [YourLoginName];
GO

Testing Script 4: Change Database Permissions

This script modifies database-level permissions, triggering the DATABASE_PERMISSION_CHANGE_GROUP.

-- Grant 'SELECT' permission on a schema
USE YourDatabaseName;
GO

GRANT SELECT ON SCHEMA::dbo TO [YourUserName];
GO

Testing Script 5: Change Login Password

This script changes a login’s password, which should trigger the LOGIN_CHANGE_PASSWORD_GROUP.

-- Change password for a login
USE master;
GO

ALTER LOGIN [YourLoginName] WITH PASSWORD = 'NewPassword';
GO

Review Audit Logs

After executing the above test scripts, review the audit logs to ensure that the events are being captured as expected. You can query the audit file directly with the following script:

-- Query the audit file to review captured events
SELECT 
    event_time, 
    action_id, 
    succeeded, 
    server_principal_name, 
    server_instance_name, 
    database_name, 
    object_name, 
    statement 
FROM 
    sys.fn_get_audit_file ('D:\Temp\SQLServer\Audits\*.sqlaudit', default, default);
GO

Make sure to adjust the path ‘D:\Temp\SQLServer\*.sqlaudit’ to the location where your audit files are stored. This function will help you view the details of each audited event, allowing you to confirm if your audit is functioning correctly.