Ensure 'user options' database flag for Cloud SQL SQL server instance is not configured

CIS GCP 6.3.4

GCP

October 4, 2026

GCP

October 4, 2026

The user options server configuration option in SQL Server is a bitmask that sets the default SET options for every new login session on the instance, so one value silently changes query semantics for every client that does not set its own options. The control requires that a Cloud SQL for SQL Server instance has no user options database flag configured, which leaves each session on the SQL Server defaults.

What the benchmark asks for

The instance must have no database flag named user options in its settings. The check is on presence, not on value. An instance with user options=0 carries the same defaults as an instance with no flag at all, and it still fails, because the audit looks for the flag in the databaseFlags collection rather than comparing the value to the default.

The control applies only to Cloud SQL for SQL Server. Cloud SQL for MySQL and Cloud SQL for PostgreSQL have no flag of this name.

How it works

user options holds an integer from 0 to 32767. Each bit turns on one SET option at login. The values that matter most in practice are:

2 sets IMPLICIT_TRANSACTIONS on, so a SELECT, INSERT, UPDATE or DELETE opens a transaction that stays open until the client commits or rolls back.
8 sets ANSI_WARNINGS on and clearing it turns the option off, so character data that is too long for a column is truncated without an error.
32 sets ANSI_NULLS on, which controls whether = NULL compares as a value or as unknown.
128 sets ARITHIGNORE on, so an overflow or a divide by zero returns NULL and produces no error message.
256 sets QUOTED_IDENTIFIER on, which decides whether a double-quoted string is an identifier or a literal.

The default is 0. The server applies the bitmask when a session logs in, and a session reads its own current mask from @@OPTIONS. Sessions that are already connected keep the mask they logged in with, so a change to the flag spreads through the instance as connection pools recycle rather than taking effect at once.

A client that sets an option explicitly wins. The SQL Server ODBC driver and the OLE DB provider set several ANSI options at connect time, and a session can call SET at any point afterwards. This is why the flag is not a control on behavior so much as a change to the starting point for clients that stay quiet.

The failure mode is not an access grant. Setting user options does not give any principal a permission it did not have. The risk here is that results change shape without anything in the application code changing. Code that was written and tested against the defaults runs against a different set of defaults after the flag is set, and the symptoms are wrong numbers and missing rows rather than an error. If the flag clears ARITHIGNORE's companion options, a report that used to fail loudly on a bad divisor starts returning NULL and the row quietly drops out of a sum.

There is a second consequence that does surface as an error. Data modification against a table that has an indexed view or an index on a computed column requires ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL and QUOTED_IDENTIFIER on, and NUMERIC_ROUNDABORT off. If user options hands a session a mask that turns one of those the wrong way, the INSERT or UPDATE fails for that session while the same statement from a client that sets its own options succeeds.

What the control does not cover

The control checks one instance-level flag. It does not stop a session from overriding any of these defaults for itself with a SET statement, whether that is SET IMPLICIT_TRANSACTIONS ON or any other, and a stored procedure can issue the same statements while it runs.

It does not cover the database-scoped equivalents. ALTER DATABASE ... SET ANSI_NULLS, ANSI_PADDING, QUOTED_IDENTIFIER and the other database options carry their own defaults for a database, and clearing the flag leaves those untouched. Compatibility level is also out of scope.

It does not cover the client. A connection string, a driver default or a JDBC property can set the same options for every session an application opens, and nothing in the instance settings records that.

It does not look at any other database flag. An instance can pass this control and still run with cross db ownership chaining on or external scripts enabled on, each of which is a separate control.

What it costs to comply

Clearing the flag changes the defaults for every session that logs in afterwards. If an application was written against the flag's mask, it starts behaving differently at the next connection, and the change arrives at whatever rate the pool recycles connections. That spread makes the cause hard to find later, so tell the application owners before the change rather than after.

The failure that is cheapest to predict is legacy code that depends on ANSI_NULLS off and compares a column to NULL with =. Those predicates stop matching rows once the session default returns to on. Code that relies on IMPLICIT_TRANSACTIONS on is the other common case, and it shows up as data that is no longer committed by an explicit COMMIT that the code never issued.

gcloud sql instances patch --clear-database-flags removes every flag on the instance, not only this one. Any flag that was set on purpose goes back to its default in the same operation. Some Cloud SQL for SQL Server flags restart the instance when they change, so a clear-all can take an instance down even though removing user options on its own does not restart it. Run the change in a low traffic window.

The case where the cost is not worth paying is a vendor application that documents a required session mask and cannot be changed. The better answer there is to set those options in that application's connection or in its session setup code, which scopes the change to one application instead of every login on the instance. That still fails the control, and the right record is an exception with the vendor requirement attached.

How to check your environment

List the instances in the project:

gcloud sql instances list

For each SQL Server instance, read the flag out of the instance settings. The command returns nothing when the flag is absent, which is the passing result:

gcloud sql instances describe <INSTANCE_NAME> --format=json | jq '.settings.databaseFlags[] | select(.name=="user options")|.value'

In the Google Cloud console, open the instance and look at the Database flags section of the Instance overview page. The flag passes when user options is not in that list.

From inside the instance, read the configured value and the value the server is currently running with:

SELECT name, value, value_in_use FROM sys.configurations WHERE name = 'user options';

To see what a session actually inherited, run DBCC USEROPTIONS on that connection. It lists the SET options in force, which is the quickest way to tell whether a client is overriding the instance default or taking it.

How to implement it

Remove the flag with a patch. To clear every flag on the instance back to its default:

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

To keep other flags, pass the full set you want to remain and leave user options out. The flag list in a patch replaces the previous list, so any flag not named is reset to its default:

gcloud sql instances patch <INSTANCE_NAME> --database-flags [FLAG1=VALUE1,FLAG2=VALUE2]

Read the current flags with gcloud sql instances describe before you build that list. In the console, open the instance, click Edit, expand the Flags section, remove the user options entry and save.

Removing the flag does not restart the instance.

To stop new instances from failing, treat the flag list as part of the instance definition in your infrastructure code. databaseFlags sits in the instance settings in the Cloud SQL Admin API resource, so it appears in Terraform plans, in Cloud Asset Inventory exports and in any policy check that reads instance configuration. A rule that rejects a user options entry at plan time is cheaper than finding it in an audit, because at plan time no application depends on the mask yet.

Where an application genuinely needs a non-default mask, move the SET statements into that application's session setup. The options then apply to one connection pool, the instance default stays clean, and a second application on the same instance is unaffected.