Back to Checks
gcp

Cloud SQL for SQL Server instance does not have the 'user options' flag configured

cloudsql_instance_sqlserver_user_options_flag
Service: cloudsqlSeverity: medium
OfficialBuilt-inVerified
Categories:vulnerabilities
Resource Type:sqladmin.googleapis.com/Instance

Cloud SQL for SQL Server instances are evaluated for the user options database flag configured with any value.

This flag sets global defaults for session SET behaviors; the check identifies instances where this global override is present.

Prowler logoProwlerprowler-provider-gcp

Risk

Global user options changes affect all sessions, impacting data integrity and availability. Disabling safe ANSI behaviors or enabling implicit transactions can alter NULL comparisons and error handling, leading to inconsistent results, lock contention, and application failures, reducing predictability and complicating auditing.

Run this check with Prowler

Run in Prowler Cloud
Run
prowler gcp --checks cloudsql_instance_sqlserver_user_options_flag

Recommendation

Leave user options unset at the instance level; keep default behavior. Control SET options explicitly at session or database scope.

  • Enforce least privilege for flag management
  • Use change control and testing before rollout
  • Monitor for configuration drift as part of defense in depth

Remediation

CLI

gcloud sql instances patch <example_resource_name> --clear-database-flags

Terraform

# Cloud SQL for SQL Server instance with no 'user options' flag set
resource "google_sql_database_instance" "<example_resource_name>" {
  name             = "<example_resource_name>"
  region           = "<example_region>"
  database_version = "SQLSERVER_2019_STANDARD"

  settings {
    tier = "db-custom-2-7680"
    # Remediation: Do NOT set a database_flags block for 'user options'
    # This omission removes/unsets the 'user options' flag so the check passes.
  }
}

Other

1In Google Cloud Console, go to Cloud SQL and open your SQL Server instance
2Click Edit
3In Database flags, locate 'user options' and click the X to remove it
4Click Save
5Allow the instance to restart to apply the change

Check Code

from prowler.lib.check.models import Check, Check_Report_GCP
from prowler.providers.gcp.services.cloudsql.cloudsql_client import cloudsql_client


class cloudsql_instance_sqlserver_user_options_flag(Check):
    def execute(self) -> Check_Report_GCP:
        findings = []
        for instance in cloudsql_client.instances:
            if "SQLSERVER" in instance.version:
                report = Check_Report_GCP(metadata=self.metadata(), resource=instance)
                report.status = "PASS"
                report.status_extended = f"SQL Server Instance {instance.name} does not have 'user options' flag set."
                for flag in instance.flags:
                    if (
                        flag.get("name", "") == "user options"
                        and flag.get("value", "") != ""
                    ):
                        report.status = "FAIL"
                        report.status_extended = f"SQL Server Instance {instance.name} has 'user options' flag set."
                        break
                findings.append(report)

        return findings

References