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.droptable.com
"cbrichards via droptable.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