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

by

Troubleshooting Guide: SQL Server Error 1980

Why This Error Happens

This error occurs when SQL Server attempts to create an index on a computed column, but the column’s expression references an object (such as a user-defined function, view, or table) that is owned by a different database principal than the owner of the table itself. SQL Server requires strict ownership chaining consistency for indexed computed columns because the engine must guarantee deterministic behavior—if the underlying object’s owner changes or differs, SQL Server cannot verify that the computation will remain safe to persist in an index. This typically happens after objects are migrated, schemas are transferred, or when a function is created by one user (e.g., dbo) while the table is created by another (e.g., AppUser).

Step-by-Step Resolution Guide

Step 1: Identify the Ownership Mismatch

  • Run the following query to check the owner of the table and the referenced object:
SELECT o.name, o.type_desc, s.name AS owner_name
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.name IN ('YourTableName', 'YourFunctionOrViewName');
  • Confirm that the schema owner differs between the table and the underlying object referenced in the computed column expression.

Step 2: Align Ownership Using ALTER AUTHORIZATION

  • Change the ownership of the underlying object (function, view, etc.) to match the table’s owner:
ALTER AUTHORIZATION ON OBJECT::dbo.YourFunctionOrViewName TO [dbo];
  • If the table itself has a non-standard owner, instead align the table to match the object:
ALTER AUTHORIZATION ON OBJECT::dbo.YourTableName TO [dbo];
  • Ensure both objects reside under the same schema if your organization enforces schema-based ownership rather than legacy dbo ownership.

Step 3: Recreate the Computed Column Index

  • Drop any failed or partial index definitions:
DROP INDEX IF EXISTS IX_ComputedColumn ON dbo.YourTableName;
  • Attempt to recreate the index:
CREATE INDEX IX_ComputedColumn ON dbo.YourTableName (YourComputedColumn);
  • Confirm no error is returned in SQL Server Management Studio (SSMS) or Azure Data Studio.

Step 4: Validate Schema Binding (If Applicable)

  • If the computed column relies on a function, ensure that function was created WITH SCHEMABINDING to prevent future ownership drift:
ALTER FUNCTION dbo.YourFunction()
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    RETURN 1;
END;
  • Re-run the index creation statement from Step 3 to confirm resolution.

Alternative or Verification Steps

  • Verify success by querying sys.indexes to confirm the new index exists on the target table without errors:
SELECT name, type_desc FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.YourTableName');
  • If ownership cannot be changed due to security policies, an alternative is to create a persisted computed column without an index (PERSISTED keyword), avoiding the ownership chain requirement entirely while still improving read performance.