Quoted Identifier ON as default

Currently my user databases, and model database, all have QUOTED IDENTIFER
set to false.
I want to help ensure that QUOTED_IDENTIFIER is set to ON under the following
conditions:
1. When a stored procedure is created (assuming the connection that is
performing the create, allows database default settings) to use the database
default settings. In other words, there is not a SET QUOTED_IDENTIFIER ON
statement when the procedure is created or altered, but instead it uses the
database default setting.
2. When a new database is created, its default setting is QUOTED IDENTIFIERS
ENABLED = TRUE.
How would one go about doing this?
--
Message posted via http://www.sqlmonster.com"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6a6bb12d8cd92@.uwe...
> Currently my user databases, and model database, all have QUOTED IDENTIFER
> set to false.
> I want to help ensure that QUOTED_IDENTIFIER is set to ON under the
> following
> conditions:
> 1. When a stored procedure is created (assuming the connection that is
> performing the create, allows database default settings) to use the
> database
> default settings. In other words, there is not a SET QUOTED_IDENTIFIER ON
> statement when the procedure is created or altered, but instead it uses
> the
> database default setting.
> 2. When a new database is created, its default setting is QUOTED
> IDENTIFIERS
> ENABLED = TRUE.
> How would one go about doing this?
For the database you will have to monitor and complain, since creating
databases cannot be rolled back.
For stored procedures you can add a DDL Trigger in each database that will
prevent stored procedure creation or modification from a connection that
does not have the required setting. EG
CREATE TRIGGER ddl_trig_enforce_quoted_identifiers
ON database
FOR CREATE_PROCEDURE, ALTER_PROCEDURE
AS
begin
declare @.quoted_identifiers varchar(5)
set @.quoted_identifiers = EVENTDATA().value(
'(/EVENT_INSTANCE/TSQLCommand/SetOptions/@.QUOTED_IDENTIFIER)[1]',
'nvarchar(max)')
if @.quoted_identifiers <> 'ON'
begin
raiserror('You must use SET QUOTED_IDENTIFIER ON to create or alter a
procedure.',16,1)
end
--select @.quoted_identifiers quoted_identifiers
end
GO
Then
SET QUOTED_IDENTIFIER off
go
create procedure foo
as
select 1 a
Fails with
Msg 50000, Level 16, State 1, Procedure ddl_trig_enforce_quoted_identifiers,
Line 13
You must use SET QUOTED_IDENTIFIER ON to create or alter a procedure.
David|||Also, the database option is largely useless as all modern APIs (and tools) will override the
database settings.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in message
news:%23TX5k4kGHHA.4920@.TK2MSFTNGP05.phx.gbl...
>
> "cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:6a6bb12d8cd92@.uwe...
>> Currently my user databases, and model database, all have QUOTED IDENTIFER
>> set to false.
>> I want to help ensure that QUOTED_IDENTIFIER is set to ON under the following
>> conditions:
>> 1. When a stored procedure is created (assuming the connection that is
>> performing the create, allows database default settings) to use the database
>> default settings. In other words, there is not a SET QUOTED_IDENTIFIER ON
>> statement when the procedure is created or altered, but instead it uses the
>> database default setting.
>> 2. When a new database is created, its default setting is QUOTED IDENTIFIERS
>> ENABLED = TRUE.
>> How would one go about doing this?
>
> For the database you will have to monitor and complain, since creating databases cannot be rolled
> back.
> For stored procedures you can add a DDL Trigger in each database that will prevent stored
> procedure creation or modification from a connection that does not have the required setting. EG
> CREATE TRIGGER ddl_trig_enforce_quoted_identifiers
> ON database
> FOR CREATE_PROCEDURE, ALTER_PROCEDURE
> AS
> begin
> declare @.quoted_identifiers varchar(5)
> set @.quoted_identifiers = EVENTDATA().value(
> '(/EVENT_INSTANCE/TSQLCommand/SetOptions/@.QUOTED_IDENTIFIER)[1]',
> 'nvarchar(max)')
> if @.quoted_identifiers <> 'ON'
> begin
> raiserror('You must use SET QUOTED_IDENTIFIER ON to create or alter a procedure.',16,1)
> end
> --select @.quoted_identifiers quoted_identifiers
> end
> GO
> Then
> SET QUOTED_IDENTIFIER off
> go
> create procedure foo
> as
> select 1 a
> Fails with
> Msg 50000, Level 16, State 1, Procedure ddl_trig_enforce_quoted_identifiers, Line 13
> You must use SET QUOTED_IDENTIFIER ON to create or alter a procedure.
>
> David
>sql