How to fix Error 1991 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: Error 1991 — Cannot Disable Clustered Index Due to Foreign Key Permission Conflict

Why This Error Happens

This error occurs when a user attempts to disable a clustered index on a table, but a foreign key constraint from another table references it, and the current user lacks ALTER permission on the referencing table. SQL Server 2025 requires that any user disabling an index also have sufficient permissions to implicitly disable the dependent foreign key constraints that rely on that index. This is typically caused by a permissions mismatch between schema ownership of the parent table and the child (referencing) table, especially in multi-schema databases.

Step-by-Step Resolution Guide

Step 1: Identify the Referencing Foreign Key and Its Owning Schema

  • Run the following query to locate the exact foreign key and referencing table mentioned in the error message:
SELECT fk.name AS ForeignKeyName, OBJECT_NAME(fk.parent_object_id) AS ReferencingTable, s.name AS SchemaName
FROM sys.foreign_keys fk
JOIN sys.tables t ON fk.referenced_object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id;
  • Confirm the schema and owner of the referencing table, since permission checks in SQL Server 2025 are schema-scoped.

Step 2: Grant ALTER Permission on the Referencing Table

  • Use SQL Server Management Studio (SSMS) 21 or sqlcmd on Windows Server 2025 to grant the required permission:
GRANT ALTER ON OBJECT::[Schema].[ReferencingTableName] TO [YourLoginOrRole];
  • If the referencing table belongs to a different schema than the clustered index table, also grant ALTER at the schema level:
GRANT ALTER ON SCHEMA::[SchemaName] TO [YourLoginOrRole];

Step 3: Disable the Foreign Key Constraint Manually Before Disabling the Index

  • Instead of relying on cascading disable behavior, manually disable the foreign key first:
ALTER TABLE [Schema].[ReferencingTableName] NOCHECK CONSTRAINT [ForeignKeyName];
  • Once the constraint is disabled, retry disabling the clustered index on the original table:
ALTER INDEX [ClusteredIndexName] ON [Schema].[TableName] DISABLE;

Step 4: Verify Role Membership and Ownership Chaining

  • Check whether the user executing the command is a member of db_ddladmin or db_owner, as these roles bypass most object-level permission checks:
EXEC sp_helprolemember 'db_ddladmin';
  • If ownership chaining is broken (different owners between parent and child tables), reassign a consistent owner using ALTER AUTHORIZATION.

Alternative or Verification Steps

  • After applying the fix, re-run the original ALTER INDEX ... DISABLE statement and confirm no Error 1991 is thrown; then check sys.indexes to verify the is_disabled flag is set to 1.
  • As an alternative, if permissions cannot be modified due to organizational policy, ask a sysadmin or db_owner role member to execute the disable operation on your behalf using EXECUTE AS USER = 'dbo' within the script.