DDL Triggers are a new feature in SQL 2005; where the use of DML triggers have been common in imposing a simple data audit strategy, you can now use DDL triggers successfully as a means for schema and object auditing. As you can image, implementing such a solution can be immensely useful in a development project.
Another feature in SQL 2005 that assist this solution is the EVENTDATA() function. This function only returns data when used inside of a DDL Trigger; scope for what the EVENTDATA function returns is set when you create the trigger. It is important to know what scope of events you want to track as it determines the content of the XML returned when the EVENTDATA function is called. You can find out more from BOL here: http://technet.microsoft.com/en-us/library/ms186438.aspx:
In this example, I am concerned mostly with capturing event data on events in the DDL_TABLE_VIEW_EVENTS and DDL_PROCEDURE_EVENTS scope.
Since DDL_TABLE_VIEW_EVENTS and DDL_PROCEDURE_EVENTS both inherit from DDL_DATABASE_LEVEL_EVENTS, they share these base properties: SQLInstance, LoginName, PostTime, SPID, ComputerName (according to BOL, however in practice I have not seen this returned in the XML schema in ALTER_TABLE and other similar events), UserName, and DatabaseName.
DDL_TABLE_VIEW_EVENTS adds the following properties: SchemaName, TSQLCommand, ObjectType with DDL_PROCEDURE_EVENTS having the same plus ObjectName. DDL_TABLE_VIEW_EVENTS does not have the ObjectName within its scope due to the child event scope that captures Statistics events – since the stat are really against an object, they carry the TargetObjectType and TargetObjectName properties.
I go into all of this because it determines how you would design your DDL logging table. You might choose to build a different table for every event scope you are attempting to log, or choose to build one table for every event. Either way, knowing the DDL Events and what information each scope returns is important for your design in both the table and your trigger. In this example, since I am going to capture information within the scope of DDL_TABLE_VIEW_EVENTS and DDL_PROCEDURE_EVENTS, my table design will be as follows; be sure to create this table before you run the script for the trigger:
CREATE TABLE [dbo].[DDLEvents](
[DDLEventKey] [int] IDENTITY(1,1) NOT NULL,
[EventLoginName] [varchar](50),
[EventUserName] [varchar](50),
[EventPostTime] [datetime],
[EventDatabaseName] [nvarchar](50),
[EventObjectSchemaName] [varchar](50) NULL,
[EventObjectName] [varchar](250) NULL,
[EventObjectType] [varchar](50) NULL,
[EventType] [varchar](50) NULL,
[EventTargetObjectType] [varchar](50) NULL,
[EventTargetObjectName] [varchar](250) NULL,
[SPID] [int],
[TSQLCommand] [varchar](max) NULL
) ON [PRIMARY]
The script to create the trigger:
create trigger [DDL_Logger]on Database
for DDL_TABLE_VIEW_EVENTS, DDL_PROCEDURE_EVENTS
as
declare @eventdata XMLset @eventdata = eventdata()
insert into dbo.DDLEvents
( EventLoginName, EventUserName, EventPostTime, EventDatabaseName , EventObjectSchemaName, EventObjectName, EventObjectType, EventType , EventTargetObjectType, EventTargetObjectName, SPID, TSQLCommand)
values
( @eventdata.value('(/EVENT_INSTANCE/LoginName)[1]','varchar(50)')
, @eventdata.value('(/EVENT_INSTANCE/UserName)[1]','varchar(50)')
, @eventdata.value('(/EVENT_INSTANCE/PostTime)[1]','datetime')
, @eventdata.value('(/EVENT_INSTANCE/DatabaseName)[1]','varchar(50)')
, @eventdata.value('(/EVENT_INSTANCE/SchemaName)[1]','varchar(50)')
, @eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]','varchar(250)')
, @eventdata.value('(/EVENT_INSTANCE/ObjectType)[1]','varchar(50)')
, @eventdata.value('(/EVENT_INSTANCE/EventType)[1]','varchar(50)')
, @eventdata.value('(/EVENT_INSTANCE/TargetObjectType)[1]','varchar(50)')
, @eventdata.value('(/EVENT_INSTANCE/TargetObjectName)[1]','varchar(250)')
, @eventdata.value('(/EVENT_INSTANCE/SPID)[1]','nvarchar(100)')
, @eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(max)')
)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [DDL_Logger] ON DATABASE
To test, all you need to do is create, alter or delete a table, view or stored procedure in the database you created the DDL Trigger in.
With this implemented, you now have basic auditing for your system – by no means comprehensive and should not replace a true versioning system, but it is a handy fallback should you be unable to acquire such a product or as a supplement to an enteprise versioning system.
Additional links:
Using the XML value method: http://technet.microsoft.com/en-us/library/ms178030.aspx
Tags: auditing,
t-sql
Categories: SQL Server