Prevent Index Changes in SQL Server With a DDL Trigger

To prevent index changes in one database, create a DDL trigger on the three index events. The trigger rolls back the statement and tells the user why.

Why Anyone Wants This

Many people have access to a database, and each one changes indexes to speed up one query. Over time a table collects indexes that nobody remembers and every write pays for. At one client, developers had created more than a hundred indexes. That was a major problem.

The cleanest fix is to give developers a role that cannot create indexes. When that is not possible today, a DDL trigger is a quick guard. A DDL trigger fires on schema statements such as CREATE INDEX, instead of on row changes. The demo uses a database named IndexGuardDemo with one table and one index.

IF DB_ID(N'IndexGuardDemo') IS NULL CREATE DATABASE IndexGuardDemo;
GO
USE IndexGuardDemo;
GO
DROP TABLE IF EXISTS dbo.Employees;
CREATE TABLE dbo.Employees (EmployeeID int IDENTITY(1,1) CONSTRAINT PK_Employees PRIMARY KEY, LastName nvarchar(50) NOT NULL, Department nvarchar(30) NOT NULL);
INSERT INTO dbo.Employees (LastName, Department) VALUES (N'Rivera', N'Sales'), (N'Kim', N'Support'), (N'Shah', N'Sales');
CREATE INDEX IX_Employees_Department ON dbo.Employees (Department);

The Trigger

A trigger on DATABASE fires for any user in that database. It lists the events it watches. Here the events are CREATE_INDEX, ALTER_INDEX and DROP_INDEX. The statement that fires the trigger has already run when the trigger starts, so the trigger undoes it with ROLLBACK. The THROW that follows gives the user a clear reason.

CREATE OR ALTER TRIGGER trg_BlockIndexChanges ON DATABASE
FOR CREATE_INDEX, ALTER_INDEX, DROP_INDEX
AS
BEGIN
    ROLLBACK TRANSACTION;
    THROW 50001, N'Index changes are blocked in this database.', 1;
END;

Now try to prevent index changes by testing the guard. The first statement creates a new index.

CREATE INDEX IX_Employees_LastName ON dbo.Employees (LastName);

Msg 50001, Level 16, State 1, Procedure trg_BlockIndexChanges, Line 6
Index changes are blocked in this database.

The statement fails and no index is created. The message names the trigger and says what to do next. A plain PRINT followed by ROLLBACK also stops the statement. It ends the batch with Msg 3609, and that message does not say why. A real message saves a support call.

What the Trigger Blocks

The ALTER_INDEX event covers more than a change of columns. Each of the next four statements fails with the same Msg 50001, as the output shows. The script runs each in its own batch.

DROP INDEX IX_Employees_Department ON dbo.Employees;
GO
ALTER INDEX IX_Employees_Department ON dbo.Employees REBUILD;
GO
ALTER INDEX IX_Employees_Department ON dbo.Employees REORGANIZE;
GO
ALTER INDEX IX_Employees_Department ON dbo.Employees DISABLE;

Rebuild and reorganize are on the list, so routine maintenance stops too. That is the cost of this trigger. The follow-up post Trigger Blocks Index Maintenance: How to Let Rebuilds Pass shows how to allow it. The trigger also applies to every user, including administrators. In the test the session belonged to a sysadmin, and the statements still failed.

The Rollback Takes the Whole Transaction

The ROLLBACK in the trigger does not undo only the index statement. It ends the open transaction, with everything inside it. The next batch inserts a row and then tries to create an index in the same transaction.

BEGIN TRANSACTION;
INSERT INTO dbo.Employees (LastName, Department) VALUES (N'Diaz', N'Sales');
CREATE INDEX IX_Employees_LastName ON dbo.Employees (LastName);
GO
SELECT @@TRANCOUNT AS OpenTransactions, (SELECT COUNT(*) FROM dbo.Employees WHERE LastName = N'Diaz') AS DiazRows;

After the error no transaction stays open, and the new row is gone. A deployment script that mixes data changes with an index change loses the data changes too. Run index changes in their own batch.

What Slips Past

A trigger on index events sees only index statements. Other statements create indexes as a side effect. A unique constraint builds a unique index, and the trigger does not fire for it. The statistics statements also run.

ALTER TABLE dbo.Employees ADD CONSTRAINT UQ_Employees_LastName UNIQUE (LastName);
UPDATE STATISTICS dbo.Employees;
CREATE STATISTICS ST_Employees_Department ON dbo.Employees (Department);
SELECT name, type_desc, is_unique_constraint FROM sys.indexes WHERE object_id = OBJECT_ID(N'dbo.Employees') ORDER BY index_id;

The unique constraint went through, and its index now exists. The same is true for a primary key created with a new table. A guard built on this trigger stops index statements, not index creation in general. Treat it as a speed bump, and combine it with permissions when the rule matters.

See and Switch Off the Trigger

A database trigger lives in the database, and the catalog lists it with its events. To change an index on purpose, disable the trigger first and enable it again right after. Write that pair into your change script, because a trigger left disabled protects nothing. Any member of db_owner, and anyone with ALTER ANY DATABASE DDL TRIGGER, can drop or disable the trigger.

SELECT t.name, t.parent_class_desc, t.is_disabled, e.type_desc AS EventName
FROM sys.triggers AS t
JOIN sys.trigger_events AS e ON e.object_id = t.object_id
WHERE t.parent_class = 0
ORDER BY e.type_desc;

DISABLE TRIGGER trg_BlockIndexChanges ON DATABASE;
CREATE INDEX IX_Employees_LastName ON dbo.Employees (LastName);
ENABLE TRIGGER trg_BlockIndexChanges ON DATABASE;

With the trigger disabled, the index was created. The ENABLE statement puts the guard back at once.

Is a Trigger the Right Tool?

You could argue that a trigger is the wrong tool, and I agree. I prefer a role without the permission to change indexes, because a permission cannot be skipped by a side effect. A trigger is easy to disable and easy to forget. Deny Drop Permission for a Table in SQL Server shows the permission approach for drops.

What to Remember

To prevent index changes with a trigger, watch CREATE_INDEX, ALTER_INDEX and DROP_INDEX. Roll back, and throw a message that explains the rule. Remember that rebuilds stop too, that constraints slip past, and that administrators are not exempt. Keep the disable and enable pair in every approved change.

When you finish, drop the demo database.

USE master;
GO
ALTER DATABASE IndexGuardDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE IndexGuardDemo;

A guard on index statements is not a ban on indexes, it is a speed bump with a message.

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