DBA - Create DDL Trigger for Auditing

Scenario: 

Sometime we have to capture Audit information such as who dropped the table, who created indexes, who altered Stored procedure etc.

Solution : 

To capture this type of information we can create DDL Trigger on Server.
Here is the list of Events for which DDL Trigger can be fired

http://technet.microsoft.com/en-us/library/ms189871(v=sql.90).aspx

I have selected few events according to my requirement. You can include or exclude events according to your requirements.

Sample Code: 

USE [master]

GO

/****** Object:  Table [dbo].[Audit_Log]    Script Date: 08/14/2013 11:21:00 ******/
SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Audit_Log]
  (
     [EventTime]    [DATETIME] NULL,
     [LoginName]    [VARCHAR](255) NULL,
     [UserName]     [VARCHAR](255) NULL,
     [DatabaseName] [VARCHAR](255) NULL,
     [SchemaName]   [VARCHAR](255) NULL,
     [ObjectName]   [VARCHAR](255) NULL,
     [ObjectType]   [VARCHAR](255) NULL,
     [DDLCommand]   [VARCHAR](max) NULL
  )
ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO 

--Create Trigger With Selected Events
/****** Object:  DdlTrigger [Log_Table_DDL]    Script Date: 08/14/2013 11:06:04 ******/
SET ANSI_NULLS ON

GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [Log_Table_DDL]
ON ALL SERVER

FOR DDL_TABLE_EVENTS,
DDL_PROCEDURE_EVENTS,
DDL_FUNCTION_EVENTS,
DDL_VIEW_EVENTS,
DDL_TRIGGER_EVENTS,
DDL_DATABASE_SECURITY_EVENTS,
CREATE_Database,DROP_DATABASE,
DDL_LOGIN_EVENTS,
DDL_INDEX_EVENTS

AS
SET ANSI_PADDING ON
DECLARE       @eventInfo XML

SET           @eventInfo = EVENTDATA()
INSERT INTO Audit_Log VALUES

(

       REPLACE(CONVERT(VARCHAR(50),

              @eventInfo.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),

       CONVERT(VARCHAR(255),

              @eventInfo.query('data(/EVENT_INSTANCE/LoginName)')),

       CONVERT(VARCHAR(255),

              @eventInfo.query('data(/EVENT_INSTANCE/UserName)')),

       CONVERT(VARCHAR(255),

              @eventInfo.query('data(/EVENT_INSTANCE/DatabaseName)')),

       CONVERT(VARCHAR(50),

              @eventInfo.query('data(/EVENT_INSTANCE/SchemaName)')),

       CONVERT(VARCHAR(255),

              @eventInfo.query('data(/EVENT_INSTANCE/ObjectName)')),

       CONVERT(VARCHAR(50),

              @eventInfo.query('data(/EVENT_INSTANCE/ObjectType)')),

       CONVERT(VARCHAR(MAX),

              @eventInfo.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)')))
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [Log_Table_DDL] ON ALL SERVER
GO