We have seen end users report SQL_Identity table error is encountered while upgrading from very older versions. Here I have taken the example of UserSettings table that might help resolve this behaviour at your end if you are facing something similar.

Note: Please update the script to match your database configuration before execution.

-- Add a new identity column to the UserSettings table
ALTER TABLE [UserSettings]
ADD Id_new3 INT IDENTITY(1,1);
GO

-- Drop existing indexes or constraints on the current identity column
DROP INDEX <Enter Index Name Here> ON [UserSettings];
DROP INDEX <Enter Another Index or Constraint Name Here> ON [UserSettings];
GO

-- Remove the original identity column
ALTER TABLE [UserSettings]
DROP COLUMN USet_SettingId;
GO

-- Rename the newly added identity column to match the original column name, later set that as primary key if needed
EXEC sp_rename 'UserSettings.Id_new3', 'USet_SettingId', 'COLUMN';
GO

-- Recreate the previously dropped indexes, only if needed
CREATE INDEX <Specify Index Name> ON [UserSettings] (USet_SettingId, <Additional Columns>);
GO

-- Enable explicit insertion into the identity column if needed
SET IDENTITY_INSERT [UserSettings] ON;
GO


Example for UserSettings table:

-- Step 1: Drop the unique index causing the conflict
DROP INDEX IDX_USet_SettingId ON UserSettings;

-- Step 2: Add new identity column
ALTER TABLE UserSettings
ADD USet_SettingID_New INT IDENTITY(1,1);

-- Step 3: Drop foreign key (temporarily) if it depends on the old column
-- Not needed in this case, since FK is on USet_UserID

-- Step 4: Drop the old column
ALTER TABLE UserSettings
DROP COLUMN USet_SettingID;

-- Step 5: Rename new column to original name
EXEC sp_rename 'UserSettings.USet_SettingID_New', 'USet_SettingID', 'COLUMN';

-- Step 6: (Optional) Add primary key if desired
ALTER TABLE UserSettings
ADD CONSTRAINT PK_UserSettings PRIMARY KEY (USet_SettingID);

-- Ensure identity insert setting is appropriate
SET IDENTITY_INSERT UserSettings ON;
GO

Explanation:

This script modifies the UserSettings table by introducing a new identity column (Id_new3) to replace the existing USet_SettingId, which may not be auto-incrementing as required. It proceeds by:

1. Adding the new identity column.

2. Dropping any associated indexes or constraints on the existing identity column.

3. Dropping the original column (USet_SettingId).

4. Renaming the new identity column to USet_SettingId. If needed, marking it as primary key.

5. Re-creating the previously dropped indexes if needed.

6. Enabling IDENTITY_INSERT if you need to insert explicit values into the identity column during migration.

Make sure to substitute placeholder values (e.g., index names and column names) with actual values relevant to your database schema.

Above code might need more tweaks as per requirement.

Hope this helps!

Seema Singla