Help Center/Relational Database Service_RDS for SQL Server/User Guide/Log Management/Managing SQL Audit Logs
Updated on 2026-03-23 GMT+08:00
View PDF
Share

Managing SQL Audit Logs

You can manually set SQL audit log policies for RDS for SQL Server instances. Major change operations on services, databases, and tables are recorded in audit log files for future query and download. This section describes how to enable and download SQL audit logs and how to configure SQL audit log ingestion to LTS.

Differences Between Audit Logs in System Logs and Those Generated by Manually Enabling SQL Audit

Table 1 Function differences

Function

How to Enable

Log Storage Location

Retention Period

Impact on Performance

Audit logs in system logs

This function is enabled by default and cannot be disabled.

VM

The audit logs rotate based on the number of files and have a short retention period.

Non-full audit data, no performance impact

Audit logs generated by manually enabling SQL audit

This function can be manually enabled and disabled.

OBS bucket

You can set the retention period on the console. The retention period is long.

The audit logs can be retained from 1 to 732 days and are retained for 7 days by default.

Non-full audit data, no performance impact

Audit log ingestion to LTS

You can enable or disable this function.

LTS

The retention period is set by LTS.

Full data, which consumes some performance resources

Introduction to Audit Logs

RDS for SQL Server Audit enables you to audit server-level and database-level groups of events and individual events. RDS for SQL Server audits consist of zero or more audit action items. Table 2 shows the server-level audit action groups and provides the equivalent RDS for SQL Server Event Class where applicable. For more information, see SQL Server Audit Action Groups and Actions.

  • The maximum size of an audit log file is 50 MB. Up to 50 audit log files can be displayed.
  • RDS for SQL Server 2008 Web and Standard Editions do not support the SQL audit function.
  • No audit is performed for job creation and modifications on parameters, server attribute parameters, SQL agent attribute parameters, and database extended attribute parameters.
  • The succeeded parameter displayed in the audit log indicates whether the event is triggered successfully. Its value cannot be null. For all events except login events, only the success or failure of the permission check (not the operation) is reported.
  • For details about the audit of table-level and column-level architecture changes, see the audit result of the SQL Server engine.
  • To read audit logs, you can obtain the audit log file name from the console and then run the following statement:

    select * from msdb.dbo.rds_fn_get_audit_file('D:\ServerAudit\audit\RDSAudit_test.sqlaudit', default, default)

    If you have already downloaded the audit log file to a local directory, log in to your local SQL Server database and then run the following statement (the local account must have the CONTROL SERVER permission):

    select * from sys.fn_get_audit_file('\\path\RDSAudit_test.sqlaudit', default, default)

Table 2 Audit action groups

Action Group Name

Description

APPLICATION_ROLE_CHANGE_PASSWORD_GROUP

This event is raised whenever a password is changed for an application role.

DATABASE_CHANGE_GROUP

This event is raised when a database is created, altered, or dropped.

DATABASE_OBJECT_CHANGE_GROUP

This event is raised when a CREATE, ALTER, or DROP statement is executed on database objects, such as schemas.

DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP

This event is raised when a change of owner for objects within database scope.

DATABASE_OBJECT_PERMISSION_CHANGE_GROUP

This event is raised when a GRANT, REVOKE, or DENY has been issued for database objects, such as assemblies and schemas.

DATABASE_OWNERSHIP_CHANGE_GROUP

This event is raised when you use the ALTER AUTHORIZATION statement to change the owner of a database.

DATABASE_PERMISSION_CHANGE_GROUP

This event is raised whenever a GRANT, REVOKE, or DENY is issued for a statement permission by any user in SQL Server for database-only events such as granting permissions on a database.

DATABASE_PRINCIPAL_CHANGE_GROUP

This event is raised when principals, such as users, are created, altered, or dropped from a database.

DATABASE_ROLE_MEMBER_CHANGE_GROUP

This event is raised whenever a login is added to or removed from a database role.

FAILED_LOGIN_GROUP

Indicates that a principal tried to log on to a SQL Server database and failed. Events in this class are raised by new connections or by connections that are reused from a connection pool.

LOGIN_CHANGE_PASSWORD_GROUP

This event is raised whenever a login password is changed by way of ALTER LOGIN statement or sp_password stored procedure.

SCHEMA_OBJECT_CHANGE_GROUP

This event is raised when a CREATE, ALTER, or DROP operation is performed on a schema.

SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP

This event is raised when the permissions to change the owner of schema object (such as a table, procedure, or function) is checked.

SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP

This event is raised whenever a grant, deny, revoke is performed against a schema object.

SERVER_OBJECT_CHANGE_GROUP

This event is raised for CREATE, ALTER, or DROP operations on server objects.

SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP

This event is raised when the owner is changed for objects in server scope.

SERVER_OBJECT_PERMISSION_CHANGE_GROUP

This event is raised whenever a GRANT, REVOKE, or DENY is issued for a server object permission by any principal in SQL Server.

SERVER_PERMISSION_CHANGE_GROUP

This event is raised when a GRANT, REVOKE, or DENY is issued for permissions in the server scope.

SERVER_PRINCIPAL_CHANGE_GROUP

This event is raised when server principals are created, altered, or dropped.

SERVER_ROLE_MEMBER_CHANGE_GROUP

This event is raised whenever a login is added or removed from a fixed server role.

SERVER_STATE_CHANGE_GROUP

This event is raised when the SQL Server service state is modified.

USER_CHANGE_PASSWORD_GROUP

This event is raised whenever the password of a contained database user is changed by using the ALTER USER statement (SQL Server 2008 is not involved).

Enabling or Downloading SQL Audit Logs

  1. Click in the upper left corner and select a region.
  2. Click in the upper left corner of the page and choose Databases > Relational Database Service.
  3. On the Instances page, click the target instance name to go to the Summary page.
  4. In the navigation pane on the left, choose Logs. On the displayed page, click the SQL Audit Logs tab.
  5. On the displayed tab page, click Set SQL Audit. In the displayed dialog box, specify parameters and click OK.

    If Audit Log Ingestion to LTS is enabled, you can switch to the analysis view in the upper right corner of the page to see more details. For more information, see Managing Log Groups.

    Enabling or setting SQL audit

    • To enable SQL audit, toggle on the switch (from to ).
    • Audit logs can be retained from 1 to 732 days and are retained for 7 days by default.
    Figure 1 RDS for SQL Server audit logs

    Disabling SQL audit

    To disable SQL audit, toggle off the switch (from to ).

    If you select the check box "I acknowledge that after audit log is disabled, all audit logs are deleted." and click OK, all audit logs will be deleted.

    Deleted audit logs cannot be recovered. Exercise caution when performing this operation.

  1. Click in the upper left corner and select a region.
  2. Click in the upper left corner of the page and choose Databases > Relational Database Service.
  3. On the Instances page, click the target instance name to go to the Summary page.
  4. In the navigation pane on the left, choose Logs. On the displayed page, click the SQL Audit Logs tab.
  5. On the displayed page, select a time range in the upper right corner, select SQL audit logs to be downloaded in the list, and click Download above the list to download SQL audit logs in batches.

    Alternatively, select an audit log and click Download in the Operation column to download an individual SQL audit log.

  1. Click in the upper left corner and select a region.
  2. Click in the upper left corner of the page and choose Databases > Relational Database Service.
  3. On the Instances page, click the target instance name to go to the Summary page.
  4. In the navigation pane, choose Logs.
  5. On the SQL Audit Logs tab page, click Ingest Logs to LTS in the upper right corner.
  6. In the displayed dialog box, select Audit log under Log Types, select a log group and log stream, and click OK.

    Figure 2 Configuring audit log ingestion to LTS
    • After this function is enabled, audit logs record all requests sent to your DB instance and are stored in LTS.
    • Once configured, there is a delay of approximately 10 minutes before the changes are applied.
    • You will be billed for enabling this function. For details, see LTS pricing details.
    • After being connected to LTS, SQL audit logs are displayed in plaintext on the LTS console. Exercise caution when performing this operation.
    • A large number of log files may be temporarily stored locally during peak hours, which may cause the storage to fill up quickly. To prevent this, enable storage autoscaling as well.
    • After this function is enabled, all audit policies are reported by default.

Parent Topic: Log Management