SQL SERVER – Trigger on Database to Prevent Table Creation

A Trigger on Database scope can reject CREATE_TABLE events. A customer required this rule, though I preferred ordinary permissions.

-- In a disposable test database, separate from business workloads:
-- CREATE TRIGGER StopTableCreation ON DATABASE FOR CREATE_TABLE AS
-- BEGIN
--     PRINT 'This database does not allow new tables.';
--     ROLLBACK TRANSACTION;
-- END;
-- GO
-- CREATE TABLE dbo.T1 (ID int); -- Expected rejection by that trigger.
-- GO
-- DROP TRIGGER StopTableCreation ON DATABASE; -- Lab cleanup.
SELECT name,is_disabled,parent_class_desc FROM sys.triggers WHERE parent_class=0;

I previously called this prevention of database creation. CREATE_DATABASE requires a different server scope. The shown trigger handles table creation. ROLLBACK rejects that operation, producing the historical message 3609.

Administrators can disable or remove the trigger. It can obstruct intended deployments and application operations. Prefer least privilege when it expresses the requirement. Review ownership and an operational escape process for any trigger.

The reproduction and cleanup are disabled lab instructions. Test rejection in a disposable database with a client that handles the aborted batch. Confirm the original trigger state after testing.

Reference: Database DDL trigger scope.

Related reading

• Comprehensive Database Performance Health Check
• Twitter

A DDL trigger is not an immutable administrator boundary, it is event-driven logic with scope and operational consequences.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.