question

colibri avatar image
colibri asked

trigger or trace to identify schema changes

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.
triggertrace
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
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)[1]', 'nvarchar(100)'), @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'), SYSTEM_USER, @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)'), @data.value('(/EVENT_INSTANCE/ObjectType)[1]', '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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock avatar image
sp_lock answered
You can track the DDL operations with a standard report in SSMS (Schema Change History). This is a report based on each database. Is this what you are looking for or an instance wide reports?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.