DDL Trigger for ALTER any object

Question

Is there a way to make a DDL trigger which fires on ALTER of any object? So far the only way I see to do this is enumerate each object type (e.g. ALTER_TABLE, ALTER_PROCEDURE, etc) when what I'd really like is something like ALTER_ANY.

Answer 1

DDL trigger which restricts ALTER Objects. You can add additional Alter objects which you wish to restrict.

SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
CREATE TRIGGER Tr_DDL_AlterObjects ON DATABASE
  WITH ENCRYPTION
  FOR ALTER_TRIGGER, ALTER_TABLE, ALTER_PROCEDURE, ALTER_SCHEMA,
    ALTER_CERTIFICATE, ALTER_FUNCTION, ALTER_APPLICATION_ROLE,
    ALTER_DATABASE, ALTER_INDEX, ALTER_ROLE, ALTER_VIEW,
    ALTER_XML_SCHEMA_COLLECTION
AS
BEGIN
    PRINT 'You do not have sufficient ALTER Privilege !'; 
    ROLLBACK;
END; 


GO
ENABLE TRIGGER Tr_DDL_AlterObjects ON DATABASE;
GO