Redgate Monitor 14

This tab displays actions related to permission changes using SQL Audit. Since we are using SQL Audit to detect these events, it does not guarantee that the operation completed successfully or that a change was made. It indicates that this action was executed in SQL Server.

This feature is in preview, so its behavior will evolve. Please note that any data collected during this phase might not be retained.

By default, the maximum number of audit files retained is 5, and each audit file is set to 100 MB. Redgate Monitor cannot ensure the security of the Audit files or tampering with the Monitor data repository.

Action being Audited:

Redgate Monitor audits the following actions:

Creating a security principal (CREATE)
Deleting a security principal (DROP)
Modifying a security principal (ALTER)
Creating a database principal (CREATE)
Deleting a database principal (DROP)
Modifying a database principal (ALTER)
Changing a database user's login (EXEC sp_change_users_login 'Update_One'...)
Auto-changing a database user's login (EXEC sp_change_users_login 'Auto_Fix'...)

Actions coming soon:

Adding and removing security principals from a server role.
Adding or removing permissions from a server principal (GRANT, DENY, REVOKE).

Below is a definition for each column on the SQL Audit grid.

Date: The date and time that the audited event was executed.
Who: The server principal that executed the action. Impersonated login will appear within brackets if impersonation was used when executing the action. Maps to the session server principal in SQL Audit.
Permission check: Determine if the session server principal had appropriate permissions to run the action. Maps to the "succeeded" column in SQL Audit.
Server: The server name.
Database: The database name.
Group: The name of the group that the server is associated with.
Tags: A list of current tags for the server.
Security principal: The server or database security principal affected by the audit event.
Security principal type: The type of the security principal.
Target server principal: The target server level principal of the action, if not already covered by the "Security principal" column.
Target database principal: The target database level principal of the action, if not already covered by the "Security principal" column.
Statement: The SQL script that was run when executing the audit event.

SQL Audit on Amazon RDS

For information on setting up SQL Audit on Amazon RDS, visit our Setup Amazon RDS SQL Audit page.

Permissions required for enabling SQL audit

For SQL Server 2022 or later, the minimum permissions required are ALTER ANY SERVER AUDIT and VIEW SERVER SECURITY AUDIT. These permissions can be granted using the SQL statements below.

GRANT ALTER ANY SERVER AUDIT TO [sql_monitor_account];
GRANT VIEW SERVER SECURITY AUDIT TO [sql_monitor_account];

For SQL Server 2019 or earlier, the CONTROL SERVER permission is the minimum required permission.

GRANT CONTROL SERVER TO [sql_monitor_account];

If the user has the sysadmin role then no additional permissions need to be granted.

The minimum required permissions are ALTER ANY SERVER AUDIT and SELECT on msdb.dbo.rds_fn_get_audit_file. These permissions can be granted using the SQL statements below.

GRANT ALTER ANY SERVER AUDIT TO [sql_monitor_account];

USE msdb;
GRANT SELECT ON OBJECT::msdb.dbo.rds_fn_get_audit_file TO [sql_monitor_account];

If the user has the sysadmin role then no additional permissions need to be granted.

Configuration changes for SQL audit

To change SQL Audit MAXSIZE and MAX_ROLLOVER_FILES settings, the user needs to disable both audit and audit specification, then they can apply the queries below and re-enable SQL Audit.

USE master;

-- Declare variables
DECLARE @MAXSIZE NVARCHAR(20) = N'100MB'; -- e.g. '100MB', '1GB','UNLIMITED'
DECLARE @MAX_ROLLOVER_FILES INT = 5;

/* Disable spec/audit if they exist */
IF EXISTS
(
    SELECT 1
    FROM sys.server_audit_specifications
    WHERE name = 'RedgateMonitorPermissionsAuditSpec'
)
BEGIN
    ALTER SERVER AUDIT SPECIFICATION [RedgateMonitorPermissionsAuditSpec]
    WITH
    (STATE = OFF);
END;

IF EXISTS
(
    SELECT 1
    FROM sys.server_audits
    WHERE name = 'RedgateMonitorPermissionsAudit'
)
BEGIN
    ALTER SERVER AUDIT [RedgateMonitorPermissionsAudit]
    WITH
    (
        STATE = OFF
    );
END;


DECLARE @sql NVARCHAR(MAX) = N'
ALTER SERVER AUDIT [RedgateMonitorPermissionsAudit]
TO FILE
(
    MAXSIZE = ' + @MAXSIZE + N',
    MAX_ROLLOVER_FILES = ' + CAST(@MAX_ROLLOVER_FILES AS NVARCHAR(20)) + N'
);';
EXEC sys.sp_executesql @sql;

/* Re-enable audit specification and audit */
ALTER SERVER AUDIT SPECIFICATION [RedgateMonitorPermissionsAuditSpec] WITH (STATE = ON);
ALTER SERVER AUDIT [RedgateMonitorPermissionsAudit] WITH (STATE = ON);
GO

To change Amazon RDS SQL Audit MAXSIZE settings, the user needs to disable both audit and audit specification, then they can apply the queries below and re-enable SQL Audit. Please note that Amazon RDS requires MAXSIZE to be between 2MB and 50MB. It also does not allow MAX_ROLLOVER_FILES changes.

USE master;

-- Declare variable
DECLARE @MAXSIZE NVARCHAR(20) = N'2MB'; -- Alter MAXSIZE between 2MB and 50MB

/* Disable spec/audit if they exist */
IF EXISTS
(
    SELECT 1
    FROM sys.server_audit_specifications
    WHERE name = 'RedgateMonitorPermissionsAuditSpec'
)
BEGIN
    ALTER SERVER AUDIT SPECIFICATION [RedgateMonitorPermissionsAuditSpec]
    WITH
    (STATE = OFF);
END;

IF EXISTS
(
    SELECT 1
    FROM sys.server_audits
    WHERE name = 'RedgateMonitorPermissionsAudit'
)
BEGIN
    ALTER SERVER AUDIT [RedgateMonitorPermissionsAudit]
    WITH
    (
        STATE = OFF
    );
END;

DECLARE @sql NVARCHAR(MAX) = N'
ALTER SERVER AUDIT [RedgateMonitorPermissionsAudit]
TO FILE
(
    MAXSIZE = ' + @MAXSIZE + N'
);';
EXEC sys.sp_executesql @sql;

/* Re-enable audit specification and audit */
ALTER SERVER AUDIT SPECIFICATION [RedgateMonitorPermissionsAuditSpec] WITH (STATE = ON);
ALTER SERVER AUDIT [RedgateMonitorPermissionsAudit] WITH (STATE = ON);
GO