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)

Actions coming soon:

Adding and removing security principals from a server role.
Adding or removing permissions from a server principal (GRANT, DENY, REVOKE).
Database actions related to database principals and database roles.

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.
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.
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

To allow Redgate Monitor to utilize SQL Audit for this, the user needs to have the sysadmin role. If not granting sysadmin role, the CONTROL SERVER permission must be granted.

GRANT CONTROL SERVER TO [sql_monitor_account];

Alternatively, if not providing the CONTROL SERVER permission, the minimum permissions required by the user for SQL Audit 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];

To allow Redgate Monitor to utilize SQL Audit for this, the user needs to have the sysadmin role. If not granting sysadmin role, the ALTER ANY SERVER AUDIT and SELECT permissions must be granted.

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];

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