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

by

Troubleshooting Guide: SQL Server Error 8104

Why This Error Happens

Error 8104 occurs when a user attempts to execute a SET operation (such as SET IDENTITY_INSERT or modifying table properties) on a table they do not own, either directly or through schema ownership. SQL Server 2025 enforces strict ownership chaining for certain DDL-like operations, meaning membership in db_owner or sysadmin roles alone is not always sufficient if the object’s schema owner differs from the executing user’s default schema. This typically arises after a schema migration, a restored database with mismatched ownership metadata, or when a login’s default schema was not explicitly mapped during object creation.

Step-by-Step Resolution Guide

Step 1: Identify the Current Object and Schema Owner

  • Run the following query to check the actual owner of the schema containing the table:
  • SELECT s.name AS SchemaName, p.name AS OwnerName FROM sys.schemas s JOIN sys.database_principals p ON s.principal_id = p.principal_id WHERE s.name = 'YourSchemaName';
  • Compare this output against the login currently executing the SET statement using SELECT CURRENT_USER;.

Step 2: Reassign Table or Schema Ownership

  • Open SQL Server Management Studio (SSMS) 21 or Azure Data Studio connected to your Windows Server 2025 instance.
  • Execute ALTER AUTHORIZATION ON OBJECT::dbo.YourTableName TO YourIntendedUser; to transfer object-level ownership directly.
  • If the issue is schema-wide, run ALTER AUTHORIZATION ON SCHEMA::YourSchemaName TO YourIntendedUser; instead.

Step 3: Correct the User’s Default Schema Mapping

  • Navigate to Object Explorer > Security > Logins in SSMS, right-click the affected login, and select Properties.
  • Under User Mapping, select the target database and verify the Default Schema field matches the schema containing the table (commonly dbo).
  • Alternatively, run: ALTER USER YourUsername WITH DEFAULT_SCHEMA = dbo;

Step 4: Grant Explicit CONTROL Permissions (If Reassignment Is Not Feasible)

  • If ownership cannot be changed due to compliance policies, grant granular control instead:
  • GRANT CONTROL ON OBJECT::dbo.YourTableName TO YourUsername;
  • Restart the SQL Server service via services.msc on Windows Server 2025 to ensure permission caches refresh cleanly.

Alternative or Verification Steps

  • Re-run the original SET statement (e.g., SET IDENTITY_INSERT dbo.YourTableName ON) logged in as the affected user to confirm the Error 8104 no longer appears.
  • If the error persists, use sp_changedbowner (legacy compatibility mode) or verify that the user is not mistakenly mapped to a different database context via the connection string in your client application.