DDL Triggers were a new feature of SQL Server 2005 and while seemingly simple, they are very powerful. DDL Triggers allow you to trap an attempted DDL operation to audit it, prevent it, or do anything you want to validate/verify/”authorize”/etc – you write the code. And, since a trigger fires as part of the transaction, you can roll it back.

In many conference demos/webcasts, etc., I have provided a sample script that prevents ddl within a [production] database. That script has been really helpful/useful but recently I thought about an update to it…

SQL Server 2005 has another new feature "execute as". While I definitely see many benefits, I’m also a bit concerned. To a certain extent, I feel that the potential for SQL Injection is actually higher. If a developer creates a poorly written/tested stored procedure (ok, therein lies the problem, really!) that includes dynamic string execution AND then uses "execute as" to essentially elevate a user with minimal privileges to a higher level (so that they don’t need to give the base object rights to the user), a malicious user could “inject” code in and actually succeed if the “execute as” user has rights to the injected code. In prior releases, and with the default behavior (execute as caller), this is not possible (which is good for security but bad for dynamically executed strings within stored procedures as base object rights are necessary).

Having said that, and since security is always a concern, my DDL Trigger only audited for the login of the user who executed the statement, not for the actual user that’s logged in. In other words, if EXECUTE AS is used (or SETUSER is used), then the context of the user executing is actually different then the logged in user. To see this shift in context, SQL Server 2005 added a new function: ORIGINAL_LOGIN().

(reading between the lines is even more frightening in that prior to SQL Server 2005, the original user could not be tracked from SETUSER. The good news is that SETUSER is ONLY allowed to be used by DBOs so it’s not as widespread as the potential for “execute as”).

OK, so how can we put all of this together? We’ll want to add the ORIGINAL_LOGIN function into our audit table in our DDL Trigger. Even if you choose NOT to rollback, at least you’ll know who performed the operation (even if from a dynamically executed string!).

USE AdventureWorks;

go

--Create a login/user - just for this exercise

CREATE LOGIN Paul WITH PASSWORD = 'PxKoJ29!07';

go

CREATE USER Paul FOR LOGIN Paul;

go

sp_addrolemember 'db_ddladmin', 'Paul'

go

CREATE SCHEMA SecurityAdministration

go

CREATE TABLE SecurityAdministration.AuditDDLOperations

(

            OpID                            int                                NOT NULL identity    

                                                                                    CONSTRAINT AuditDDLOperationsPK

                                                                                                PRIMARY KEY CLUSTERED,

            OriginalLoginName    sysname                     NOT NULL,

            LoginName                 sysname                     NOT NULL,

            UserName                   sysname                     NOT NULL,

            PostTime                     datetime                     NOT NULL,

            EventType                   nvarchar(100)            NOT NULL,

            DDLOp                        nvarchar(2000)          NOT NULL

);

go

GRANT INSERT ON SecurityAdministration.AuditDDLOperations TO public;

go

CREATE TRIGGER PreventAllDDL

ON DATABASE

WITH ENCRYPTION

FOR DDL_DATABASE_LEVEL_EVENTS

AS

DECLARE @data XML

SET @data = EVENTDATA()

RAISERROR ('DDL Operations are prohibited on this production database. Please contact ITOperations for proper policies and change control procedures.', 16, -1)

ROLLBACK

INSERT SecurityAdministration.AuditDDLOperations

                        (OriginalLoginName,

                         LoginName,

                         UserName,

                         PostTime,

                         EventType,

                         DDLOp)

VALUES   (ORIGINAL_LOGIN(), SYSTEM_USER, CURRENT_USER, GETDATE(),

   @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),

   @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') )

RETURN;

go

--Test the trigger.

CREATE TABLE TestTable (col1 int);

go

DROP TABLE SecurityAdministration.AuditDDLOperations;

go

EXECUTE AS LOGIN = 'Paul' -- note: Remember, Paul is a DDL_admin

go

DROP TABLE SecurityAdministration.AuditDDLOperations;

go

REVERT;

go

SELECT * FROM SecurityAdministration.AuditDDLOperations;

go

DROP TRIGGER PreventAllDDL ON DATABASE;

go

DROP TABLE SecurityAdministration.AuditDDLOperations;

go

DROP SCHEMA SecurityAdministration;

go

DROP USER Paul;

go
DROP LOGIN Paul;
go

So, have fun testing with this one.

Thanks for reading!

kt