DATABASE Trigger

DDL Trigger, 2013年的时候用的是AdministratorLog表，2021年更新为Administration.DDL_Log摘自 https://techcommunity.microsoft.com/t5/azure-sql/logging-schema-changes-in-a-database-using-ddl-trigger/ba-p/1950343

-- =============================================
-- Author:					Andreas Wolter, Microsoft
-- Create date:			11-2020
-- Revision History:	
-- Description:			This script demonstrates the implementation of a DDL-Trigger to log all DDL events to a database table for internal use.
--								it is not meant to act as a security-measure
-- Article:					 Logging Schema-changes in a Database using DDL Trigger
-- Applies to:				SQL Server, Azure SQL Database, Azure SQL Database Managed Instance
--=============================================

SET QUOTED_IDENTIFIER ON

-- Change Database
-- in Azure SQL Database remove the "USE Database" statements and open a new connection if necessary
USE DDLWatchdogTrigger
GO
/* Create Schema if not exists */

IF SCHEMA_ID('Administration') IS NULL
BEGIN
	EXEC( 'CREATE SCHEMA Administration;' );
END


 -- if the DDL Trigger already exists we need to diable it to drop and recreate the logging table
IF EXISTS (SELECT * FROM sys.triggers
    WHERE parent_class = 0 AND name = 'Trig_Log_DDL_DATABASE_LEVEL_EVENTS')
DISABLE TRIGGER Trig_Log_DDL_DATABASE_LEVEL_EVENTS ON DATABASE 
GO

/* Create the DDL_Log table */
 IF EXISTS (SELECT * FROM sys.tables
    WHERE SCHEMA_NAME(schema_id) = 'Administration' AND name = 'DDL_Log')
DROP TABLE Administration.DDL_Log
GO

CREATE TABLE Administration.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
EXEC sys.sp_addextendedproperty
		@name			= N'Description'
	,	@value			= N'Stores all relevant DDL Statements against the current database, inserted by DDL Trigger.'
	,	@level0type	= N'SCHEMA'
	,	@level0name	= N'Administration'
	,	@level1type	= N'TABLE'
	,	@level1name	= N'DDL_Log'
GO

EXEC sys.sp_addextendedproperty
		@name			= N'Referenced by'
	,	@value			= N'PROC: del_DDL_Log_by_oldest_date, TRIGGER: Trig_Log_DDL_DATABASE_LEVEL_EVENTS'
	,	@level0type	= N'SCHEMA'
	,	@level0name	= N'Administration'
	,	@level1type	= N'TABLE'
	,	@level1name	= N'DDL_Log'
GO

ALTER TABLE Administration.DDL_Log
	ADD CONSTRAINT PKCL_Administration_DDL_Log_DDL_Log_ID
		PRIMARY KEY CLUSTERED (DDL_Log_ID)
GO


/* Drop and Re-Create the DDL Trigger */

IF EXISTS (SELECT * FROM sys.triggers
    WHERE parent_class = 0 AND name = 'Trig_Log_DDL_DATABASE_LEVEL_EVENTS')
DROP TRIGGER Trig_Log_DDL_DATABASE_LEVEL_EVENTS ON DATABASE
GO

CREATE 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(50)
		,	@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 Administration.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;

SET QUOTED_IDENTIFIER OFF

GO

SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sys.procedures
    WHERE SCHEMA_NAME(schema_id) = 'Administration' AND name = 'del_DDL_Log_by_oldest_date')
DROP PROCEDURE Administration.del_DDL_Log_by_oldest_date
GO

CREATE PROCEDURE Administration.del_DDL_Log_by_oldest_date
	@oldest_date	datetime2(0)
AS

/*
-- =============================================
Author:					Andreas Wolter, Microsoft
Create date:			11-2020
Revision History:	
Description:			Remove old rows from DDL_Log

Execution example:
	
EXEC	Administration.del_DDL_Log_by_oldest_date
		@oldest_date = '2020-11-22'

-- =============================================
*/
SET NOCOUNT ON;
SET ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL ON;

DELETE FROM Administration.DDL_Log
	WHERE PostTime < @oldest_date	-- oldest records date to keep

GO


EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'Maintenance Procedure: Removes old rows from Administration.DDL_Log-Table' , @level0type=N'SCHEMA',@level0name=N'Administration', @level1type=N'PROCEDURE',@level1name=N'del_DDL_Log_by_oldest_date'
GO


-- === / Begin SECURITY

GRANT INSERT
	ON [Administration].[DDL_Log]
	TO public
GO

-- === / End SECURITY


BEGIN TRY
	ENABLE TRIGGER Trig_Log_DDL_DATABASE_LEVEL_EVENTS
	ON DATABASE;
	PRINT 'DDL Watchdog-Trigger active :)'
END TRY
BEGIN CATCH
	PRINT 'Something went wrong'   
	SELECT   
        ERROR_NUMBER() AS ErrorNumber  
       ,ERROR_MESSAGE() AS ErrorMessage;  
END CATCH;



/* =============== Clean up Code
-- run this if you want to drop all related Objects


IF EXISTS (SELECT * FROM sys.procedures
    WHERE SCHEMA_NAME(schema_id) = 'Administration' AND name = 'del_DDL_Log_by_oldest_date')
DROP PROCEDURE Administration.del_DDL_Log_by_oldest_date
GO

IF EXISTS (SELECT * FROM sys.triggers
    WHERE parent_class = 0 AND name = 'Trig_Log_DDL_DATABASE_LEVEL_EVENTS')
DROP TRIGGER Trig_Log_DDL_DATABASE_LEVEL_EVENTS ON DATABASE
GO

 IF EXISTS (SELECT * FROM sys.tables
    WHERE SCHEMA_NAME(schema_id) = 'Administration' AND name = 'DDL_Log')
DROP TABLE Administration.DDL_Log
GO

IF SCHEMA_ID('Administration') IS NULL
BEGIN
	EXEC( 'DROP SCHEMA Administration;' );
END


=============== */ 



/*--=== ALTERNATE SECURITY implementation using a least privileged account during Trigger-execution

If INSERT for Public is not ok, the alternative is to use a specifi User for the Trigger Execution Context.
This is how this could look like:

--===  Create User to write to the DDL-Log-Table and potentially other internal error log tables if not exists
IF USER_ID('DBLogWriter') IS NULL
BEGIN
	CREATE USER DBLogWriter WITHOUT LOGIN
END

-- sticking to best practice to use roles for permission assignment
IF NOT EXISTS (SELECT * FROM sys.database_principals
	WHERE type_desc = 'DATABASE_ROLE'
		AND name = 'RL_DBLogWriter')
BEGIN
	CREATE ROLE RL_DBLogWriter
END

ALTER ROLE RL_DBLogWriter
	ADD MEMBER DBLogWriter
GO

-- now we can grant the Insert-permission onto the whole new schema, even if the table does not exist yet
GRANT INSERT
	ON SCHEMA::Administration
	TO RL_DBLogWriter
GO

-- now change the Tigger header to use:
... WITH EXECUTE AS 'DBLogWriter'

*/