Troubleshooting Guide: SQL Server Error 9938 — “Cannot find the specified user or role”

Why This Error Happens

Error 9938 typically occurs when a database operation (such as ALTER AUTHORIZATION, sp_changedbowner, or a DDL/security-related statement) references a user or role that does not exist in the target database’s sys.database_principals catalog. This often happens because the principal was deleted, never created in that specific database, or exists at the server level (as a login) but was never mapped to a database user. It can also surface after a database restore or migration where user mappings became orphaned, meaning the SID no longer matches an existing login.

Step-by-Step Resolution Guide

Step 1: Verify the User or Role Exists in the Target Database

• Connect via SQL Server Management Studio (SSMS) or sqlcmd to the specific database mentioned in the error.
• Run the following query to check for the principal:

  SELECT name, type_desc, sid 
  FROM sys.database_principals 
  WHERE name = 'YourUserOrRoleName';

• If the query returns zero rows, the principal genuinely does not exist in this database context.

Step 2: Check for Orphaned Users (Server-to-Database Mapping Issues)

• Run sp_validatelogins or the following query to identify mismatched SIDs between the server login and database user:

  SELECT dp.name AS DBUser, dp.sid 
  FROM sys.database_principals dp
  LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
  WHERE sp.sid IS NULL AND dp.type IN ('S','U');

• If an orphaned user is found, re-link it using:

  ALTER USER [YourUserName] WITH LOGIN = [CorrespondingServerLogin];

Step 3: Recreate the Missing User or Role

• If the principal was deleted entirely, recreate it. For a login-mapped user, use:

  CREATE USER [YourUserName] FOR LOGIN [YourUserName];

• For a database role, use:

  CREATE ROLE [YourRoleName] AUTHORIZATION dbo;

• Ensure you are executing this in the correct database context (USE [YourDatabaseName];) before running the command.

Step 4: Review the Calling Script or Application Code

• Check any stored procedures, deployment scripts (DACPAC/BACPAC), or application connection logic that explicitly references the user/role name.
• Look for case-sensitivity issues or trailing whitespace in the name, especially if the server collation is case-sensitive (e.g., SQL_Latin1_General_CP1_CS_AS).
• Update the script to reference the correct, currently valid principal name.

Alternative or Verification Steps

• Verify the Fix: Re-run the original failing command or deployment script. Additionally, query sys.database_principals again to confirm the user/role now appears with the correct type_desc (e.g., SQL_USER or DATABASE_ROLE).
• Alternative Solution: If the database was recently restored from a backup taken on a different server, run EXEC sp_change_users_login 'Auto_Fix', 'YourUserName'; (for backward compatibility scenarios) or use the ALTER USER...WITH LOGIN syntax in Step 2 to resolve SID mismatches in bulk.