Cómo auditar cambios de estructura en tu SQL

USE [master]
GO

CREATE SERVER AUDIT [AUDIT_BASES]
TO FILE 
(	FILEPATH = N'F:\tmp\auditorias\'
	,MAXSIZE = 0 MB
	,MAX_ROLLOVER_FILES = 2147483647
	,RESERVE_DISK_SPACE = OFF
) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE)

GO

USE [AdventureWorks2022]
GO


CREATE DATABASE AUDIT SPECIFICATION [Cambios de Esquemas]
FOR SERVER AUDIT [AUDIT_BASES]
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON)
GO

drop table if exists demo_auditoria
create table demo_auditoria (id int)

alter table demo_auditoria add c3 int

create index ix11 on demo_auditoria (id)

ALTER INDEX ix11 on demo_auditoria REBUILD

-- buscar en auditorias

select log_file_path ,log_file_name  
from sys.server_file_audits where name='audit_bases'

SELECT a.name,a.class_desc,
auditsql.event_time,
auditsql.server_principal_name ,
auditsql.database_principal_name ,
auditsql.object_name,
auditsql.statement  
FROM sys.fn_get_audit_file('F:\tmp\auditorias\*.sqlaudit', DEFAULT, DEFAULT) auditsql
inner join sys.dm_audit_actions a
on
a.action_id = auditsql.action_id 
where class_desc ='object'
and statement not like '%alter index%'
order by 1 desc
;

-- MODIFICAMOS UN SP



create or alter procedure dbo.usp_get_auditorias
as
select 'print maxiaccotto.com'
go

alter procedure dbo.usp_get_auditorias
as
select 'print triggerdb.com'
go

-- BORRAMOS EL SP
DROP PROC dbo.usp_get_auditorias

-- creamos una tabla temporal

create table #demo1 (c1 int)

----------------------------------------------------
---- USANDO TRIGGER DDL
----------------------------------------------------

drop table if exists DDL_Log

CREATE TABLE DDL_Log
(
		DDL_Log_ID			int identity			NOT NULL
	,	EventType				nvarchar(50)		NOT NULL
	,	PostTime				datetime2(2)		DEFAULT SYSDATETIME()
	,	SPID						int						NOT NULL
	,	ServerName			nvarchar(100)	NOT NULL
	,	LoginName			nvarchar(100)	NOT NULL
	,	OriginalLogin			nvarchar(100)	NOT NULL
	,	UserName				nvarchar(100)	NOT NULL
	,	Application			nvarchar(250)	NOT NULL
	,	DatabaseName		nvarchar(100)	NOT NULL
	,	SchemaName		nvarchar(100)	NOT NULL
	,	ObjectName			nvarchar(100)	NOT NULL
	,	ObjectType			nvarchar(100)	NOT NULL
	,	TSQLCommand		nvarchar(max)	NOT NULL
	,	EventData				xml					NOT NULL
)
GO

CREATE OR ALTER TRIGGER Trig_Log_DDL_DATABASE_LEVEL_EVENTS
	ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
/*
-- =============================================
Author:					Andreas Wolter, Microsoft
Create date:			11-2020
Revision History:	

Description:			Database-scope DDL-Trigger to log all Schema-changes

Permissions:			DDL Triggers are executed under the context of the caller - or can be executed under a specific user name using the EXECUTE AS-clause
							Unless a specific User account is used, it needs to be made sure that every potential user who can run DDL statements has also INSERT-Permission to the DDL_Log-Table. It may be fine to use public.
-- =============================================
*/
SET NOCOUNT ON;
SET ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL ON;
-- XML data operations, such as @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), require both to be ON.

DECLARE
			@EventData			xml
		,	@EventType			nvarchar(1)
		,	@TSQLCommand	nvarchar(max)
		,	@PostTime			datetime2(2)
		,	@SPID					int
		,	@ServerName		nvarchar(128)
		,	@LoginName			nvarchar(128)
		,	@Original_Login		nvarchar(128)
		,	@UserName			nvarchar(128)
		,	@Application			nvarchar( 250 )
		,	@DatabaseName	nvarchar(128)
		,	@SchemaName		nvarchar(128)
		,	@ObjectName		nvarchar(128)
		,	@ObjectType			nvarchar(100)

SET @EventData		= EVENTDATA()
SET @EventType		= @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(50)' )
SET @TSQLCommand	= @EventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)' )
--CONVERT(NVARCHAR(max), @EventData.query('data(//TSQLCommand//CommandText)'))
SET @PostTime			= @EventData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime2(2)' )
SET @SPID				= @EventData.value('(/EVENT_INSTANCE/SPID)[1]', 'int' )
SET @ServerName		= HOST_NAME()
SET @LoginName		= SYSTEM_USER
SET @Original_Login	= ORIGINAL_LOGIN()
SET @UserName		= USER_NAME()
SET @Application		= COALESCE(APP_NAME(), '** NA **' )
SET @DatabaseName	= DB_NAME()
SET @SchemaName	= CASE WHEN (COALESCE(@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname' ), '** no schema **') = '') THEN '** no schema **' ELSE COALESCE(@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname' ), '** no schema **') END	   -- some events like "GRANT" on a Database return empty string for schema instead of NULL
SET @ObjectName		= @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname' )
SET @ObjectType		= @EventData.value('(/EVENT_INSTANCE/ObjectType)[1]', 'sysname' )

-- disallowing the removal of the DDL-Log Table
-- Disable or Drop the trigger beforehand
IF		@EventType	= 'DROP_TABLE'
  AND	@ObjectName	= 'DDL_Log'
  AND	@SchemaName	= 'Administration'
	BEGIN
		ROLLBACK
	END

-- Filter out operations that do not need to be looged such as Index Maintenance
IF (@EventType NOT IN (
				'UPDATE_STATISTICS'
			--,	'ALTER_INDEX'	-- We do want to include Disabling of indexes
		)
	AND NOT (@EventType = 'ALTER_INDEX' AND @TSQLCommand NOT LIKE '%DISABLE%')
	)
BEGIN

	INSERT INTO DDL_Log
			   (EventType
			   ,SPID
			   ,ServerName
			   ,LoginName
			   ,OriginalLogin
			   ,UserName
			   ,Application
			   ,DatabaseName
			   ,SchemaName
			   ,ObjectName
			   ,ObjectType
			   ,TSQLCommand
			   , EventData)
		 VALUES
			   (@EventType
			   ,@SPID
			   ,@ServerName
			   ,@LoginName
			   ,@Original_Login
			   ,@UserName
			   ,@Application
			   ,@DatabaseName
			   ,@SchemaName
			   ,@ObjectName
			   ,@ObjectType
			   ,@TSQLCommand
			   ,@EventData)


END;

---- demo

drop table DBO.DEMO20

CREATE TABLE DBO.DEMO20 (ID INT)

ALTER TABLE DBO.DEMO20 ADD C1 INT

CREATE INDEX IX1 ON DEMO20(C1)

SELECT * FROM DBO.DDL_Log 

-- probamos romperlo

DISABLE TRIGGER [Trig_Log_DDL_DATABASE_LEVEL_EVENTS] ON DATABASE

SP_RENAME 'DDL_LOG','DDL_LOG22'

ENABLE TRIGGER [Trig_Log_DDL_DATABASE_LEVEL_EVENTS] ON DATABASE

SELECT * FROM DDL_LOG22


CREATE TABLE DBO.ROTO1 (ID INT)

SELECT * FROM DBO.ROTO1

CREATE INDEX IX2 ON DEMO20(C1)