TodoSQL Blog de SQL Server en español

En la medida que crece la necesidad de auditar la actividad en nuestros servidores SQL, revisamos qué herramientas nativas traen las nuevas versiones del motor.
Adicionalmente al Profiler (herramienta cliente), a las trazas en background (del lado del servidor), a los DML triggers y a la auditoría de login-ids, aparecen en la versión 2005 de SQL los DDL triggers, es decir triggers que se disparan ante eventos de Data Definition Language (create, drop, alter).
Existen DDL triggers que se pueden definir a nivel de servidor completo (por la naturaleza del objeto que auditan, ej: creación/alter/drop de un login) o con un alcance de base de datos -para todas las BDs. o para alguna en particular-.
Estos triggers podrían registrar información útil como pista de auditoría como la cuenta Windows de la session que lo realize, desde qué host, usuario de BD, fecha, base, etc, tanto en una tabla que se haya creado para tal fin , como también escribir el evento en el Error log de SQL y en el Application Log del Event Viewer, que podría ser atrapado por MOM, si esta opción está más acorde a cómo trabajan en tu organización.

Acá hay una tablita con respecto a lo que hablábamos de los eventos que pueden capturarse y su alcance, que se puede encontrar en los Books 2005:

Para facilitar la definición también es posible definir eventos en forma global, es decir por grupos. Por ejemplo si uno quiere registrar la actividad de DDL relacionada con una base de datos, no es necesario definir un trigger DDL para las tablas por un lado, para las vistas por otro, otro trigger para definición-alter-drop de stored procedures. Se define un solo trigger para toda la base de datos (DDL_DATABASE_LEVEL_EVENTS):
La siguiente es la lista de los ‘event groups’ donde muestra claramente qué eventos grupales engloban subeventos:

Vamos ahora por unos ejemplos.

El primero es de DDL trigger con scope de todo el Servidor SQL.
En el mismo se graba un mensaje en el Log de SQL y en el Application Log de Windows, donde aparece como Source: MSSQLSERVER y EVENT: 17061.

CREATE TRIGGER servertrigger
ON ALL SERVER
FOR DDL_LOGIN_EVENTS
AS
DECLARE @data XML
DECLARE @MESSAGE varchar(255)
DECLARE @comando varchar(255)
DECLARE @Usuario NVARCHAR(100)
DECLARE @Server NVARCHAR(100)
SET @data = EVENTDATA()
SET @Usuario = SYSTEM_USER
SET @Server = @@servername
SELECT @ comando= EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)') +
EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')

SELECT @MESSAGE = 'DDL en SERVER: ' + @Server + ';' + @Usuario + ';' + @comando
EXEC master.dbo.xp_logevent 50001, @MESSAGE, informational
GO

Ahora vamos por un ejemplo a nivel base de datos. En este caso se graba un registro en una tabla creada para tal fin y también se escribe en Logs. Se utiliza un evento global para capturar cualquier actividad de DDl que ocurra en la base.

USE [basex]
GO
CREATE TRIGGER [ddlenbasex]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
DECLARE @MESSAGE varchar(255)
DECLARE @DBNAME NVARCHAR(128)
DECLARE @Usuario NVARCHAR(100)
DECLARE @comando NVARCHAR(128)
SET @DBNAME = DB_NAME()
SET @data = EVENTDATA()
SET @Usuario = SYSTEM_USER
SET @comando = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
INSERT DDL_log
(Fecha, Login, Usuario, Evento, Sentencia)
VALUES
(GETDATE(),
CONVERT(nvarchar(100), SYSTEM_USER),
CONVERT(nvarchar(100), CURRENT_USER ),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') )
SELECT @@MESSAGE = 'DDL en base de datos: ' + @DBNAME + ';' + @Usuario + ';' + @comando
EXEC master.dbo.xp_logevent 50001, @@MESSAGE, informational
GO

Hasta la próxima !!!