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

by

Why This Error Happens

Error 7972 occurs when the SQL Server Security Catalog encounters a mismatched or orphaned entry between a database principal’s UID (User ID) and its corresponding Security Identifier (SID) in master.sys.server_principals. This typically happens after a database restore/migration from an older instance, a failed Always Encrypted enclave attestation, or corruption in the internal metadata tables that map logins to database users. In SQL Server 2025, this is also frequently triggered by a broken link between a contained user and the Entra ID (Azure AD) authentication cache on Windows Server 2025 hosts.

Step-by-Step Resolution Guide

Step 1: Identify the Orphaned UID

  • Open SQL Server Management Studio (SSMS) and connect to the affected instance.
  • Run the following query to locate the orphaned UID reported in the error:
SELECT dp.name, dp.sid, dp.type_desc 
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');
  • Cross-reference the numeric UID from the error message with the principal_id column to confirm the exact user causing the fault.

Step 2: Repair the Security Mapping

  • Use sp_change_users_login (legacy) or the 2025-native ALTER USER syntax to re-link the orphaned UID to a valid login:
ALTER USER [YourUserName] WITH LOGIN = [YourServerLogin];
  • If the login no longer exists on the server, recreate it first via CREATE LOGIN [YourServerLogin] WITH PASSWORD = '...'; before re-mapping.

Step 3: Rebuild the Security Catalog Cache

  • Stop the SQL Server service via services.msc or PowerShell:
Stop-Service -Name 'MSSQLSERVER' -Force
  • Clear the stale security cache located at:
    C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Security\SecurityCache.dat
  • Restart the service:
Start-Service -Name 'MSSQLSERVER'

Step 4: Validate Windows Authentication Integration

  • On Windows Server 2025/Windows 11, open secpol.msc and confirm the SQL Server service account still has “Log on as a service” rights.
  • If using Entra ID hybrid authentication, run Test-SqlEntraIdConnection (new 2025 cmdlet) to confirm the token cache is synchronized.

Alternative or Verification Steps

  • Verify the fix by re-running the query in Step 1 — it should return zero rows, confirming no orphaned UIDs remain.
  • If the error persists, run DBCC CHECKCATALOG('YourDatabaseName') WITH NO_INFOMSGS; to rule out deeper metadata corruption, and restore from the last known-good backup if catalog corruption is confirmed.