INF: How to Determine the Current Settings for @@options

MORE INFORMATION

Run the following script as the system administrator (SA) with either ISQL or ISQL/w:

   use master
   go
   if (exists (select * from sysobjects
      where name = 'sp_currentopts'))
      drop procedure sp_currentopts
   go
   if (exists (select * from sysobjects
      where name = 'sysuseropts'))
      drop table sysuseropts
   go
   create table sysuseropts
   (optid      int      NOT NULL,
   options_set    varchar(25) NOT NULL)
   go
   insert into sysuseropts values (0,'NO OPTIONS SET')
   insert into sysuseropts values (1,'DISABLE_DEF_CNST_CHK')
   insert into sysuseropts values (2,'IMPLICIT_TRANSACTIONS')
   insert into sysuseropts values (4,'CURSOR_CLOSE_ON_COMMIT')
   insert into sysuseropts values (8,'ANSI_WARNINGS')
   insert into sysuseropts values (16,'ANSI_PADDING')
   insert into sysuseropts values (32,'ANSI_NULLS')
   insert into sysuseropts values (64,'ARITHABORT')
   insert into sysuseropts values (128,'ARITHIGNORE')
   insert into sysuseropts values (256,'QUOTED_IDENTIFIER')
   insert into sysuseropts values (512,'NOCOUNT')
   insert into sysuseropts values (1024,'ANSI_NULL_DFLT_ON')
   insert into sysuseropts values (2048,'ANSI_NULL_DFLT_OFF')
   go
   grant select on sysuseropts to public
   go
   create procedure sp_currentopts as
   if @@options <> 0
      select options_set
      from master.dbo.sysuseropts
      where (optid & @@options) > 0
   else
      select options_set
      from master.dbo.sysuseropts
      where optid = 0
   go
   grant execute on sp_currentopts to public
   go
				

If you then run sp_currentopts, you will get a result set listing the current user options set for your connection. For example, suppose a database administrator (DBA) runs the following commands:

   sp_configure 'user options', 1400
   go
   reconfigure
   go
				

A user who then logged on and ran sp_currentopts would receive the following:

   options_set
   -------------------------
   ANSI_WARNINGS
   ANSI_PADDING
   ANSI_NULLS
   ARITHABORT
   QUOTED_IDENTIFIER
   ANSI_NULL_DFLT_ON
				

Likewise, if a user logs on to a system where the sp_configure 'user options' setting is set to 0 (zero) and then issues a SET ANSI_WARNINGS ON command, sp_currentopts would return the following:

   options_set
   -------------------------
   ANSI_WARNINGS