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

by

Troubleshooting Guide: SQL Server Error 1987

Why This Error Happens

Error 1987 occurs when SQL Server attempts to perform an operation—such as creating, altering, or enabling a constraint, index, trigger, or other object—on a resource (like a table, constraint, or index) that has been explicitly disabled. This is typically caused by a prior administrative action (intentional or accidental) that disabled the object, or by a schema migration script that left the object in a disabled state. The operation cannot proceed because SQL Server enforces object-state integrity and will not act on disabled components until they are explicitly re-enabled.

Step-by-Step Resolution Guide

Step 1: Identify the Disabled Object

  • Open SQL Server Management Studio (SSMS) and connect to the affected instance.
  • Run the following query to locate disabled constraints or indexes referenced in the error message:
SELECT name, type_desc, is_disabled 
FROM sys.indexes 
WHERE is_disabled = 1;

SELECT name, type_desc, is_disabled 
FROM sys.check_constraints 
WHERE is_disabled = 1;
  • Cross-reference the object name from the error message (the %.*ls placeholders) with the query results to confirm which object is disabled.

Step 2: Re-Enable the Disabled Object

  • For a disabled index, run:
ALTER INDEX [IndexName] ON [SchemaName].[TableName] REBUILD;
  • For a disabled constraint (e.g., CHECK or FOREIGN KEY), run:
ALTER TABLE [SchemaName].[TableName] 
WITH CHECK CHECK CONSTRAINT [ConstraintName];
  • For a disabled trigger, run:
ENABLE TRIGGER [TriggerName] ON [SchemaName].[TableName];

Step 3: Validate Permissions and Ownership

  • Confirm the executing login has ALTER permission on the schema or object by checking:
SELECT * FROM fn_my_permissions('SchemaName.TableName', 'OBJECT');
  • Ensure the SQL Server service account (running under Windows Server 2025) has not been restricted via Group Policy or local security policy, which can sometimes block schema-level operations.

Step 4: Check for Automated Scripts or Jobs

  • Review SQL Server Agent Jobs and any deployment/migration scripts (DACPAC, Flyway, Liquibase, etc.) that may have intentionally disabled the object as part of a bulk-load or ETL process.
  • If disabling was intentional for performance reasons, ensure a corresponding “re-enable” step exists later in the script and wasn’t skipped due to an error upstream.

Step 5: Restart Dependent Services (If Needed)

  • If the object remains locked in a disabled state after re-enabling, restart the SQL Server service via services.msc or:
Restart-Service -Name 'MSSQLSERVER' -Force
  • This clears any stale metadata cache that might be preventing the state change from taking effect.

Alternative or Verification Steps

  • Re-run the original operation (the query or job that triggered Error 1987) to confirm it now completes without error.
  • If the object cannot be re-enabled due to data integrity violations (e.g., a CHECK constraint failing on existing rows), use WITH NOCHECK temporarily to re-enable it without validation, then clean up invalid data before re-validating with WITH CHECK CHECK CONSTRAINT.