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

by

Why This Error Happens

This error occurs because disabling a clustered index on a base table requires SQL Server to also modify any indexed (materialized) views that reference that table, since the view’s schema depends on the underlying clustered index structure. The current login lacks the ALTER permission on the referencing view itself, even if it has sufficient rights on the base table. This is common in environments with strict schema-separation security models or when operations are run under a service account with limited scope.

Step-by-Step Resolution Guide

Step 1: Identify the Referencing View and Its Owner

  • Run the following query to locate dependent indexed views:
SELECT referencing_schema_name, referencing_entity_name 
FROM sys.dm_sql_referencing_entities('SchemaName.TableName', 'OBJECT');
  • Note the exact view name reported in Error 1972 — this is the object missing permissions.
  • Cross-check the view’s owner using sys.objects to confirm if it differs from the table owner.

Step 2: Grant ALTER Permission on the Referencing View

  • Open SQL Server Management Studio (SSMS) and connect using an account with sysadmin or db_owner rights.
  • Execute the following, replacing placeholders with your actual schema/view names:
GRANT ALTER ON OBJECT::SchemaName.ViewName TO [DOMAIN\UserOrServiceAccount];
  • If using Windows Server 2025 with Azure AD/Entra ID integrated logins, verify the account sync status via sys.server_principals.

Step 3: Drop and Recreate the Index (If Permissions Alone Don’t Resolve It)

  • In some cases, the clustered index on the view itself must be dropped first before altering the base table index:
DROP INDEX IF EXISTS IX_ViewIndexName ON SchemaName.ViewName;
  • Proceed with disabling the original clustered index on the table:
ALTER INDEX IX_TableIndexName ON SchemaName.TableName DISABLE;
  • Recreate the view’s clustered index afterward to restore materialization.

Step 4: Validate Role-Based Access Control (RBAC) Settings

  • Check Windows Server 2025 Group Policy settings if the SQL Server login uses Windows Authentication.
  • Ensure the service account running SQL Server Agent (if automating this task) has been granted the same ALTER permissions explicitly, not just inherited through a role.

Alternative or Verification Steps

  • Verify the fix by re-running the original ALTER INDEX ... DISABLE command on the base table; it should complete without Error 1972.
  • As an alternative, if permission changes are restricted by corporate policy, ask a db_owner or sysadmin to execute the disable/rebuild operation on your behalf using EXECUTE AS USER = 'owner_account' within the script.