Troubleshooting Guide: Error 12820 — spmigrateusertocontained Fails on SQL Login with Password

Why This Error Happens

Error 12820 occurs because sp_migrate_user_to_contained is designed to migrate database users that map to Windows logins or Windows groups into contained database users. When the stored procedure encounters a database user backed by a SQL Server Authentication login with a password, or a user type other than a standard SQL Login mapping (such as a certificate-mapped or asymmetric key-mapped user), it cannot safely migrate the authentication context into the contained database boundary. This restriction exists because contained database users require explicit password assignment at the user level, and the migration procedure does not automatically transfer or re-hash login credentials for security reasons.

Step-by-Step Resolution Guide

Step 1: Identify the User Type Causing the Conflict

• Run the following query against the target database to inspect user mappings:

  SELECT dp.name, dp.type_desc, sp.name AS login_name, sp.type_desc AS login_type
  FROM sys.database_principals dp
  LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
  WHERE dp.type IN ('S', 'U', 'G');

• Confirm whether the user is tied to a SQL Login (type_desc = 'SQL_LOGIN') rather than a Windows Login or Group.

Step 2: Manually Create a Contained Database User Instead

• Since sp_migrate_user_to_contained only supports Windows-based principals, manually convert the SQL Login user by creating a contained user with a password:

  CREATE USER [YourUserName] WITH PASSWORD = 'StrongPassword123!';

• If the user already exists as a non-contained SQL user, drop and recreate it:

  DROP USER [YourUserName];
  CREATE USER [YourUserName] WITH PASSWORD = 'StrongPassword123!';

Step 3: Verify Database Containment Settings

• Ensure the database containment type is set to PARTIAL before attempting any contained user operations:

  SELECT name, containment, containment_desc
  FROM sys.databases
  WHERE name = 'YourDatabaseName';

• If containment is set to NONE, update it:

  ALTER DATABASE [YourDatabaseName] SET CONTAINMENT = PARTIAL;

Step 4: Re-Run Migration Only for Eligible Windows-Based Users

• Filter your migration script to exclude SQL Logins and only target Windows logins/groups:

  EXEC sp_migrate_user_to_contained
      @username = N'DOMAIN\WindowsUser',
      @rebind_indexes = 1,
      @print_info = 1;

• Confirm the @username parameter never references a SQL Login account.

Step 5: Update Application Connection Strings (If Applicable)

• If the SQL Login user was migrated manually with a new password, update any application or service connection strings in web.config, appsettings.json, or ODBC/DSN configurations on Windows Server 2025 to reflect the new contained user credentials.

Alternative or Verification Steps

• Verify the fix by querying sys.database_principals again and confirming the authentication_type_desc column shows DATABASE for the migrated or newly created user.
• If manual creation is not feasible for bulk users, consider scripting a loop that filters only Windows-authenticated accounts from sys.server_principals before invoking sp_migrate_user_to_contained, avoiding SQL Login entries entirely.