I am looking into setting up a trigger or trace to identify when a schema change occurs. We have a out of control "DBA" new-hire and I'd like to be immediately notified when they make unplanned changes to prevent more chaos than there already is. Please advise! Thanks.
We parse out these DDL changes and store them in a table instead of sending an email out, but you can take this example to start you off: CREATE TABLE [dbo].[SchemaChange]( [PostTime] [datetime] NULL, [DB_User] [nvarchar](100) NULL, [Event] [nvarchar](100) NULL, [TSQL] [nvarchar](max) NULL, [Login] [varchar](255) NULL, [ObjectName] [varchar](255) NULL, [ObjectType] [varchar](30) NULL, [ApplicationName] [varchar](255) NULL, [GSObjectExists] [bit] NULL ) ON [PRIMARY] GO CREATE TRIGGER [SchemaChangeTrigger] ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS DECLARE @data xml SET @data = EVENTDATA() INSERT dbo.SchemaChange (PostTime, DB_User, Event, TSQL, [Login], ObjectName, ObjectType, ApplicationName) VALUES (GETDATE(), CONVERT(nvarchar(100), CURRENT_USER), @data.value('(/EVENT_INSTANCE/EventType)', 'nvarchar(100)'), @data.value('(/EVENT_INSTANCE/TSQLCommand)', 'nvarchar(max)'), SYSTEM_USER, @data.value('(/EVENT_INSTANCE/ObjectName)', 'nvarchar(100)'), @data.value('(/EVENT_INSTANCE/ObjectType)', 'nvarchar(2000)'), APP_NAME()) ; Basically, we are using a database trigger to monitor for DDL events. We then parse the DDL event data (xml data privided via the EVENTDATA() function). We pick out the interesting things and place them in a audit table. This is a rather old implementation, but does the job for us. You could use this to then feed a database mail queue instead of an audit table. I'm sure that the use of extended events would be possible too, but have not looked into it, as this supplies us with the data we need.