Skip to content

Revoke SQL Agent Role Assigned From Public

Description

The Microsoft SQL Server Agent is controlled through access to the msdb database (if installed). The msdb database has the following roles (in order from least to most privileged):

1) SQLAgentUserRole 2) SQLAgentReaderRole 3) SQLAgentOperatorRole

If the 'public' role is assigned to any of these agent roles, there would be potential for escalation of privilege, depending on the capabilities of the agent role.

For more information, see SQL Server Agent Fixed Database Roles

Rationale

The SQL Agent roles grant access to privileged information and actions. Low privileged users or public should not be a member of these roles.

Applies To

  • Databases

Tags

This rule is applied when the following tags are present:

Tag With Value
secureclouddb/provider aws
secureclouddb/service rds
secureclouddb/engine sqlserver

Default Rule

const { isEmptyArray } = module
/**
 * @param {Object} databaseSettings - database settings object
 * @returns {boolean} true if guest user has no proxies
 */
function validate(databaseSettings) {
    const success = isEmptyArray(databaseSettings.users) ||
                    isEmptyArray(
                        databaseSettings.users.filter(user =>
                            user.sqlserver &&
                            user.sqlserver.source === 'sys.database_principals' &&
                            user.sqlserver.name === 'public' &&
                            user.sqlserver.parentDatabase === 'msdb' &&
                            !isEmptyArray(user.sqlserver.proxies)))

    return {
        success,
    }
}

validate(databaseSettings)