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

by

Why This Error Happens

Error 2101 occurs when SQL Server attempts to map a server-level principal (such as a role membership or permission grant) to a database user, but that user is mapped to a SID that has no corresponding login in the sys.server_principals table. This typically happens after a database restore or attach operation from a different server instance, where the database users retain their original SIDs but the matching logins were never created or were dropped on the destination server. It can also occur when a login is deleted while database users still reference it internally, leaving “orphaned users” that SQL Server cannot resolve at the server level.

Step-by-Step Resolution Guide

Step 1: Identify the Orphaned User

  • Open SQL Server Management Studio (SSMS) and connect to the affected instance.
  • Run the following query against the impacted database to list orphaned users:
EXEC sp_change_users_login 'Report';
  • Note the exact username and SID reported in the output, as this maps directly to the %.*ls placeholder in Error 2101.

Step 2: Recreate or Remap the Missing Login

  • If the login does not exist, create a new SQL login with the same name:
CREATE LOGIN [UserName] WITH PASSWORD = 'StrongPassword123!';
  • If the login exists but has a mismatched SID, remap the orphaned user using:
ALTER USER [UserName] WITH LOGIN = [UserName];
  • For Windows Authentication users, ensure the corresponding Active Directory account still exists and is reachable on Windows Server 2025 domain controllers.

Step 3: Validate Server-Level Role Assignments

  • Check if the user was supposed to be a member of a fixed server role (e.g., sysadmin, dbcreator) and reassign it manually if needed:
ALTER SERVER ROLE sysadmin ADD MEMBER [UserName];
  • Confirm that the login has the correct default database set, especially if the original database context was lost during migration.

Step 4: Restart SQL Server Services (If Needed)

  • Open Services.msc on Windows Server 2025 / Windows 11.
  • Restart the SQL Server (MSSQLSERVER) service to clear any cached principal mappings.
  • Re-run the failing operation (e.g., permission grant or role assignment) to confirm the error no longer appears.

Alternative or Verification Steps

  • Run SELECT * FROM sys.database_principals WHERE type = 'S' AND sid NOT IN (SELECT sid FROM sys.server_principals); to confirm no orphaned users remain in the database.
  • If the login still cannot be created due to SID conflicts, use sp_change_users_login 'Auto_Fix', 'UserName' as a quick alternative to auto-repair the mapping without manually specifying the login.