Auditar Base de datos usando Trigger DDL

Recientemente un compañero me pregunto si existía forma de identificar en que fecha se agrego una columna a una tabla en la base de datos, en ese momento respondí que no aunque mi respuesta no fue completa.

Quiero en este blog compartir como debió terminar esa conversación .
SQL Server nos permite auditar lo que sucede en nuestra Base de datos con diferentes mecanismos como C2 Audit ,SQL Server Audit o incluso una traza y desde la versión de SQL 2005 se agrego el uso de trigers DDL(Data Definition Language)
Como siempre hago hincapié en que el objetivo de tener diferentes formas de atacar un problema es para usar lo más adecuado a nuestras necesidades y circustancias, por ejemplo si tenemos problemas de desempeño activar un mecanismo como C2 Audit nos podría generar aun más problemas, aun mas quizá solo nos interesa auditar cambios a la estructura de nuestra BD y no un nivel granular.

Para nuestro caso utilizaremos desencadenadores DDL para auditar cambios en la estructura de nuestra Base de datos, específicamente capturaremos todos los eventos del tipo DDL_DATABASE_LEVEL_EVENTS,a continucación se muestra un arbol de eventos para tener presentes que tipo de acciones estaremos auditando.

Los eventos se recuperan con la función EVENTDATA la cual retorna un XML con el ID del proceso, la fecha, el evento y la sentencia ejecutada


El script para crear y probar nuestro disparador DDL es :


CREATE DATABASE AuditDDL –Creamos l

use AuditDDL

Create schema Audit


CREATE TABLE Audit.DDL_DATABASE_LEVEL_EVENTS_LOG
(
ID uniqueIdentifier primary key Default NewId()
, Fecha datetime Not null
, DB_Session_User nvarchar(100) Not null default Session_User
, DB_System_User nvarchar(100) Not null default System_User
, DB_User_Name nvarchar(100) Not null default User_Name()
, DB_Nombre varchar(100) Not null default Db_Name()
, Host nvarchar(50) Not null default Host_Name()
, Evento nvarchar(1000) Not null
, Sentencia nvarchar(Max) Not null
)


GO


CREATE TRIGGER LogEventos
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
Set NoCount ON
DECLARE @data XML
,@Definicion varchar(1000)
SET @data = EVENTDATA();

INSERT Audit.DDL_DATABASE_LEVEL_EVENTS_LOG
( ID
, Fecha
, DB_Session_User
, DB_System_User
, DB_User_Name
, DB_Nombre
, Host
, Evento
, Sentencia )
VALUES
(NewId(),
GETDATE(),
CONVERT(nvarchar(100), Session_User),
CONVERT(nvarchar(100), System_User),
CONVERT(nvarchar(100), User_Name()),
CONVERT(nvarchar(100), Db_Name()),
CONVERT(nvarchar(100), Host_Name()),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
) ;
END;
GO


Se que en la Web existen ejemplos similares e indudablemente en las referencias de Microsoft siempre encontraremos cada dato que necesitamos, mi idea es solo compartir algún ejemplo y en su caso mi interpretación ,creo que un ejemplo adicional nunca está de mas.