Disabling SET Statements / Forcing SET Options in SQL Server 2008R2/2012

I am currently working with a MSSQL 2008R2 Database which uses the following SET-Commands frequently:

SET ANSI_NULLS { ON | OFF }
SET QUOTED_IDENTIFIER { ON | OFF }
SET ANSI_PADDING { ON | OFF }

While I was researching these Settings I stumbled across the following Note for ANSI_NULLS on Microsofts technet (Link):

In a future version of Microsoft SQL Server ANSI_PADDING will always be ON and any applications that explicitly set the option to OFF will produce an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Because of this I would like to enforce a "global" Setting. I have been searching for the better part of a day but as I understand it you can always change these settings. To be completely sure I would like to ask here:

Is it possible to either enforce a Setting like ANSI_NULLS ON or to disable the possibility of changing these Settings on "any" level. I would like to use this to test if the Database would "survive" if the ability to set a specific option to OFF is disabled.

I hope I was able to describe my problem. This is not my first language. Thanks in advance.

PS: On StackOverflow I was advised to Ask this Question here too. Original Question: Here

Answers


There is no way to enforce a global setting or prevent people running

SET ANSI_NULLS OFF
SET ANSI_PADDING OFF

You can get a quick idea if your application is using any deprecated features by running

SELECT *
FROM   sys.dm_os_performance_counters
WHERE  object_name LIKE '%Deprecated%'
       AND cntr_value > 0
ORDER  BY cntr_value DESC 

If you do find areas of concern then you could set up a Server Side trace capturing the deprecation events.

This also returns the session id and SQL Handle of the offending batch to allow you to investigate further if you do see any events.

A slightly more sophisticated approach would be to set up a notification procedure to retrieve the text associated with the SQL Handle more quickly to reduce the probability that the offending SQL is no longer in the cache when you come to analyse the results.

Or you could probably use extended events to retrieve the TSQL along with the event.


Need Your Help

Silverlight WCF data service call back returns on UI thread

multithreading silverlight mvvm wcf-data-services silverlight-5.0

I am using Silverlight 5 with WCF data service and I have installed Silverlight WCF data Service client library.