how to ignore the SSMS (sql server management studio) sql server ansi settings?

Question

The options in Management Studio

On Management studio Tools->Options->Query Execution->SQL Server there are options that are set and these prevail over the same options that are set on database level.

The Options at Database level

on management studio you right-click on a database and you can see what are the defaults for a number of options: (in yellow on the picture below)

using sys.databases you can get hold of these options using T_SQL.

The Options At Session Level

Using sys.dm_exec_sessions you can get hold of the options as they are set for the current session.

Questions

Is there a way to make sure the database setting will prevail over the management studio settings?

I thought about creating a plan, and use OPTION (USE PLAN) but that has other consequences that is beyond the scope of this question.

Answer 1

If you are asking whether it is possible to enforce the database defaults for every connection that ever happens on the server, then no. Those defaults only take effect if something contradictory isn't set at the connection level.

In any individual batch in SSMS you can use statements like SET ANSI_NULLS ON, SET ANSI_NULLS OFF, SET QUOTED_IDENTIFIER ON, and so on, to control the way the following statements are interpreted.

With stored procedures it is a little more complicated:
When a procedure is created it remembers the current values for the ANSI_NULLS and QUOTED_IDENTIFIER options and uses them within any call to that procedure, but the same is not the case for other settings like ARITHABORT and ANSI_PADDINGS - for those you need to use SET <setting> ON/OFF in the procedure to fix the behaviour. See the Interoperability section of CREATE PROCEDURE (Transact-SQL) in the product documentation for more description of this situation.