Monitoreando operaciones DDL y DCL usando fn_trace_gettable de SQL Server

Hola, chicos,
¡Buenas tardes!

En esta publicación demostraré cómo identificar los eventos DDL y DCL que ocurren en la instancia de SQL Server. Esto nos permite identificar qué usuario creó/cambió/eliminó un objeto, o quién agregó/eliminó un usuario en un rol del sistema o rol de base de datos y varias cosas más sin la necesidad de crear ningún control o monitoreo interno, utilizando solo el rastreo estándar de SQL Server, donde consultaremos sus datos a través de la función fn_trace_gettable.

Hace un tiempo publiqué el post aquí. Cómo crear un activador de auditoría para registrar la manipulación de objetos en SQL Server lo que permitió crear un trigger de servidor que realiza esta auditoría. Aunque la función fn_trace_gettable devuelve gran parte de la información disponible en este control interno, este disparador permite versionar objetos modificados, como procedimientos, funciones, tablas, etc. En otras palabras, si desea automatizar el versionado del código fuente de objetos en la base de datos, el mejor post es el mencionado anteriormente.

Recordando los conceptos de DDL y DCL

Creo que estos conceptos ya los conocéis bien la mayoría de vosotros, pero reforzaré qué es cada cosa para que no quede ninguna duda:

Operaciones DDL (lenguaje de definición de datos)
Se utilizan para definir o cambiar estructuras o esquemas de bases de datos.

CREAR: se utiliza para crear objetos en la base de datos.
ALTER – Cambia la estructura de un objeto.
DROP: elimina un objeto de la base de datos.
TRUNCATE: elimina todos los registros de una tabla, incluido el espacio asignado.
COMENTARIO: agrega comentarios a los objetos.
RENAME – Cambia el nombre de un objeto

Operaciones DCL (lenguaje de control de datos)
Se utilizan para administrar permisos y privilegios de usuario sobre ciertos objetos en la instancia de SQL Server.

GRANT: otorga acceso a un usuario a un objeto determinado.
REVOKE: elimina el acceso de un usuario a un objeto determinado

Identificación de tipos de eventos de seguimiento

Usando la consulta a continuación, podemos identificar rápidamente todos los EventClass que podemos usar en la función ::fn_trace_gettable:

DECLARE @id INT = ( SELECT id FROM sys.traces WHERE is_default = 1 )
 
SELECT DISTINCT
    eventid,
    name
FROM
    fn_trace_geteventinfo(@id) A
    JOIN sys.trace_events B ON A.eventid = B.trace_event_id

Identificación de eventos DDL y DCL en la instancia de SQL Server

Con la consulta a continuación es posible identificar quién fue la última persona en cambiar un objeto específico en la instancia (Ej: Procedimiento Almacenado), quién agregó un usuario específico a un rol, quién eliminó una tabla, quién ejecutó un comando TRUNCATE, etc., y todo esto sin tener que crear ningún control distinto a los ya disponibles y activados por defecto en SQL Server.

DECLARE @Ds_Arquivo_Trace VARCHAR(255) = (SELECT SUBSTRING([path], 0, LEN([path])-CHARINDEX('\', REVERSE([path]))+1) + '\Log.trc' FROM sys.traces WHERE is_default = 1)

SELECT
    A.HostName,
    A.ApplicationName,
    A.NTUserName,
    A.NTDomainName,
    A.LoginName,
    A.SPID,
    A.EventClass,
    B.name,
    A.EventSubClass,
    A.TextData,
    A.StartTime,
    A.ObjectName,
    A.DatabaseName,
    A.TargetLoginName,
    A.TargetUserName
FROM
    [fn_trace_gettable](@Ds_Arquivo_Trace, DEFAULT) A
    JOIN master.sys.trace_events B ON A.EventClass = B.trace_event_id
WHERE
    A.EventClass IN ( 164, 46, 47, 108, 110, 152 ) 
    AND A.StartTime >= GETDATE()-7
    AND A.LoginName NOT IN ( 'NT AUTHORITY\NETWORK SERVICE' )
    AND A.LoginName NOT LIKE '%SQLTELEMETRY$%'
    AND A.DatabaseName != 'tempdb'
    AND NOT (B.name LIKE 'Object:%' AND A.ObjectName IS NULL )
    AND NOT (A.ApplicationName LIKE 'Red Gate%' OR A.ApplicationName LIKE '%Intellisense%' OR A.ApplicationName = 'DacFx Deploy')
ORDER BY
    StartTime DESC

¡Eso es todo, amigos!
Gracias por visitarnos y nos vemos en el próximo post.