title: Disabling Index operations for certain tables in SQL Server 2008 R2 other than DDL trigger
body: <p>Is there a way to disable certian index DDL operation (create, drop and alter index) for a list of tables in MS SQL Server 2008 R2?</p>
  
  <p>What I was trying to do is to create a DDL trigger that catch these events and roll them back, but it seems that all ddl trigers are after triggers and if table is very large this cause performance issues.</p>
  
  <p>The trigger I am currently using is the following:</p>
  
  <pre><code>CREATE TRIGGER index_guard
      ON DATABASE
      FOR CREATE_INDEX, DROP_INDEX, ALTER_INDEX
      AS
          DECLARE @object_name NVARCHAR(50);
          DECLARE @table_name NVARCHAR(50);
          DECLARE @target_object_type NVARCHAR(20);
          DECLARE @object_type NVARCHAR(20);
          DECLARE @lookup_value NVARCHAR(100);
  
          DECLARE @protected_indexes TABLE (Name NVARCHAR(50))
          INSERT INTO @protected_indexes
          SELECT Name FROM (VALUES ('TABLE1/IX_IdName'), ('TABLE2/IX_NameId')) AS tbl(Name)
  
          SELECT @object_name = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)');
          SELECT @table_name = EVENTDATA().value('(/EVENT_INSTANCE/TargetObjectName)[1]','nvarchar(max)');
          SELECT @target_object_type = EVENTDATA().value('(/EVENT_INSTANCE/TargetObjectType)[1]','nvarchar(max)');
          SELECT @object_type = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)');
  
          IF @object_type = 'INDEX' AND @target_object_type = 'TABLE'
          BEGIN
              SET @lookup_value = @table_name + '/' + @object_name
              IF EXISTS (SELECT 1 FROM @protected_indexes A WHERE A.Name = @lookup_value)
              BEGIN
                  ROLLBACK
              END
          END
  </code></pre>