DDL Triggers

[ptheriault]

I thought I would post some of the DDL triggers thtat I've been using. I hope you find them as usefull as I have.

I use this trigger on DEV boxes to notify my if a developers has created a new database. You can use it on PROD boxes if you want to issue a rollback to not allow the db creation.

CREATE TRIGGER ddl_trig_database 
ON ALL SERVER 
FOR CREATE_DATABASE 
AS 
    PRINT 'Database Created.'
    SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

EXEC msdb.dbo.sp_send_dbmail     @profile_name = 'Default',
    @recipients = 'dba@###.com',     @subject = 'New Database Created On MyDevBox.'

GO

I use this trigger on all servers to notify me of LOGIN changes like new users added. I have also created a log table that I insert the results to.

USE msdb
GO
CREATE TABLE ddl_log (PostTime datetime, 
                      DB_User nvarchar(100), 
                      Event nvarchar(100), 
                      TSQL nvarchar(2000));
GO


CREATE TRIGGER [DDL_TRIG_LOGIN]  ON ALL SERVER  FOR  DDL_LOGIN_EVENTS
AS 
DECLARE @data XML
SET @data = EVENTDATA()
INSERT msdb.dbo.ddl_log 
   (PostTime, DB_User, Event, TSQL) 
   VALUES 
   (GETDATE(), 
   CONVERT(nvarchar(100), CURRENT_USER), 
   @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), 
   @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;

PRINT 'Login Event Issued. Notifying sysadmin.'
EXEC msdb.dbo.sp_send_dbmail     @profile_name = 'Default',
    @recipients = 'dba@****.com',     @subject = 'Login Event On Server.'
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [DDL_TRIG_LOGIN] ON ALL SERVER

This one I got from SQL Server magazine. It prevents any DDL Event on a database. It also creates and enters data into a log table.

CREATE TABLE DDLAudit
(
	AuditID 	int	NOT NULL identity 
				CONSTRAINT DDLAuditPK
				PRIMARY KEY CLUSTERED,
	LoginName	sysname	NOT NULL,
	UserName	sysname	NOT NULL,
	PostDateTime datetime	NOT NULL,
	EventType	varchar(100)	NOT NULL,
	DDLOp	varchar(2500)	NOT NULL
)
go
CREATE TRIGGER RestrictDDL
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS 

EXECUTE AS USER = 'DBO'

DECLARE @errordata XML
SET @errordata = EVENTDATA()
RAISERROR ('You can not perform this action on a production database. Please contact the DBA department for change procedures.', 16, -1)
ROLLBACK
INSERT DDLAudit
		(LoginName, 
		 UserName,
		 PostDateTime,
		 EventType,
		 DDLOp)
VALUES   (SYSTEM_USER, ORIGINAL_LOGIN(), GETDATE(), 
   @errordata.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(100)'), 
   @errordata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(2500)') ) 
RETURN;
GO

- Paul
- If at first you don't succeed, find out if the loser gets anything.