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

by

Troubleshooting Guide: Error 12823 – spmigrateusertocontained Cannot Find Login

Why This Error Happens

This error occurs when the database user you are attempting to migrate to a contained user is not mapped to a valid SQL Server login at the instance level, meaning the SID link between sys.database_principals and sys.server_principals is broken or missing. This commonly happens with orphaned users, users created directly in the database without a corresponding login, or users whose login was dropped after the user was created. It can also occur if the user is a built-in principal (like guest or dbo) that the stored procedure does not support for containment conversion.

Step-by-Step Resolution Guide

Step 1: Verify the User-to-Login Mapping

  • Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to your Windows Server 2025 instance.
  • Run the following query to check for orphaned users:
  EXEC sp_change_users_login 'Report';
  • Confirm whether the specific user referenced in the error has a matching SID in sys.server_principals.

Step 2: Re-Link or Recreate the Missing Login

  • If the login is missing, create a new SQL login matching the user’s existing SID using:
  CREATE LOGIN [LoginName] WITH PASSWORD = 'StrongPassword123!', SID = 0x0101000000000000...;
  • Alternatively, if the login exists but is mismatched, remap it using:
  ALTER USER [UserName] WITH LOGIN = [LoginName];
  • Ensure the SID value is copied exactly from the sys.database_principals table for that user.

Step 3: Exclude Unsupported System Principals

  • Check if the user causing the error is dbo, guest, sys, or INFORMATION_SCHEMA.
  • If so, exclude these from your migration script since sp_migrate_user_to_contained does not support converting built-in principals.
  • Update your migration script path (typically located in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Scripts\) to filter these out using a WHERE clause on type_desc = 'SQL_USER'.

Step 4: Re-run the Migration Procedure

  • Execute the stored procedure again against the specific user:
  EXEC sp_migrate_user_to_contained 
      @username = N'UserName', 
      @rename = N'copy_login_name', 
      @disablelogin = N'true';
  • Confirm the Database Engine service account under Windows Server 2025 has sufficient permissions (ALTER ANY USER and CONTROL on the database).

Alternative or Verification Steps

  • Verify: Query sys.database_principals and check that the authentication_type_desc column now reads DATABASE instead of INSTANCE, confirming successful containment.
  • Alternative: If the login truly cannot be recovered, drop the orphaned user entirely using DROP USER [UserName] and recreate it fresh as a contained database user with CREATE USER [UserName] WITH PASSWORD = 'StrongPassword123!'.