SQL Server 2005 introduces DDL Triggers

When you wanted to audit changes for the underlying schema in earlier versions of SQL Server and it was very difficult, now with SQL Server 2005 introduces DDL Triggers to address this issue. A DDL Trigger can now be created either at a server or database level and can be set to fire on creation, alteration, or deletion of virtually every SQL Server object type.

-- Example of DDL Trigger @ Database Level:
CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT 'You must disable Trigger "safety" to drop or alter tables!'
ROLLBACK ;

In the next example, a DDL trigger prints a message if any CREATE LOGIN, ALTER LOGIN, or DROP LOGIN event occurs on the current server instance. It uses the EVENTDATA function to retrieve the text of the corresponding Transact-SQL statement.

-- Example of DDL Trigger @ Server Level:
CREATE TRIGGER ddl_trig_login
ON ALL SERVER
FOR DDL_LOGIN_EVENTS
AS
PRINT 'Login Event Issued.'
SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
GO
DROP TRIGGER ddl_trig_login
ON ALL SERVER
GO