Microsoft® SQL Server® 2012 Bible

906

Part VII: Monitoring and Auditing

Preventing Database Object Changes

DDL triggers can execute code, including a transaction rollback. This could prohibit anyone

from making server- or database-level changes.

The following code is a simple example of a rollback DDL trigger blocking any stored proce-

dures from being altered in the database:

CREATE TRIGGER WonderfulProc

ON DATABASE

FOR ALTER_PROCEDURE, DROP_PROCEDURE

AS

Set NoCount ON

Raiserror ('These Procs may not be altered or dropped!',16,1)

Rollback

To test the DDL trigger, the next few commands attempt to modify the procedure so it won’t

print “SQL Rocks!”:

DROP PROC dbo.uspGetBillOfMaterials

Result:

Msg 50000, Level 16, State 1, Procedure WonderfulProc, Line 6

These Procs may not be altered or dropped!

Msg 3609, Level 16, State 2, Procedure QuickProc, Line 3

The transaction ended in the trigger. The batch has been aborted.

And

ALTER PROC dbo.uspGetBillOfMaterials

AS

Print 'Oracle Rocks!'

Result:

Msg 50000, Level 16, State 1, Procedure WonderfulProc, Line 6

These Procs may not be altered or dropped!

Msg 3609, Level 16, State 2, Procedure QuickProc, Line 3

The transaction ended in the trigger. The batch has been aborted.

With DDL triggers, you can write your own system to prevent object changes that disagree

with your shop’s policies, but a more strategic solution might be to use policy-based man-

agement, documented in Chapter 20, “Policy Based Management.”

c36.indd 906c36.indd 906 7/31/2012 10:03:10 AM7/31/2012 10:03:10 AM

http://www.it-ebooks.info