A somewhat technical post on MS SQL Server encrypted triggers.

It turns out that MS SQL Server 2005 has an issue with encrypted triggers in the model database. We’ve created an encrypted database level trigger on DDL operations in all databases including the model database so that when a new database is created the trigger will be created in the new database as well. The problem we’ve encountered is that the encrypted triggers are not correctly copied to the new database.

For example, here is the code for creating the triggers:

exec sp_MSForeachdb
‘use ?;
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
IF EXISTS (SELECT * FROM sys.triggers
WHERE parent_class = 0 AND name = ”TEST_DDL_TRIGGER”)
DROP TRIGGER TEST_DDL_TRIGGER
ON DATABASE;’,
‘?’,
‘USE ?;
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
EXECUTE(”CREATE TRIGGER TEST_DDL_TRIGGER
ON DATABASE WITH ENCRYPTION
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
— Do something…
END;”)';
Now, let’s test this:
create database test;
GO;
use test;
create table tt (id int);

And voila -

Msg 102, Level 15, State 1, Procedure TEST_DDL_TRIGGER, Line 1
Incorrect syntax near ‘0xfa86′.

So, is there any MS SQL Server expert out there who can shed some light on this behavior? It looks like a bug to me.