Réparer les utilisateurs SQL Server orphelins après restauration

Une restauration peut réussir alors que l'application ne peut toujours pas accéder aux données. Une cause fréquente est un utilisateur orphelin: la base référence un SID de login absent de l'instance cible. Recréer un login avec le même nom visible ne recrée pas nécessairement le même SID.

Séparez authentification et autorisation. Le login établit une identité au niveau de l'instance. L'utilisateur de base représente cette identité dans une base et y porte rôles et permissions. La restauration apporte l'utilisateur, mais pas automatiquement tous les logins et dépendances de l'instance.

Diagnostiquer par le SID

Exécutez cette lecture dans la base restaurée avec une connexion administrative disposant d'une visibilité suffisante sur les principaux du serveur.

-- SQL-authenticated, instance-mapped users in the current database.
SELECT dp.name AS DatabaseUser, dp.sid AS DatabaseUserSid,
       dp.authentication_type_desc
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp ON sp.sid = dp.sid
WHERE dp.type = 'S'
  AND dp.authentication_type_desc = 'INSTANCE'
  AND dp.principal_id > 4
  AND sp.principal_id IS NULL
ORDER BY dp.name;

La requête cible volontairement les utilisateurs à authentification SQL associés à un login d'instance. Elle ne couvre pas universellement groupes Windows, utilisateurs contenus, certificats ou identités externes. Un utilisateur WITHOUT LOGIN n'est pas cassé parce qu'il n'a aucun login serveur.

La visibilité des métadonnées compte. Un compte limité peut manquer certains logins et signaler de faux orphelins. Confirmez les résultats dans le contexte administratif prévu avant de changer les associations.

Examinez séparément le login cible voulu: SID, état activé, type d'authentification et fonction. Un nom identique est un indice, pas une autorisation de relier les identités. Deux environnements peuvent réutiliser ce nom pour des services différents.

Distinguez aussi login désactivé, base par défaut indisponible, mauvaise chaîne de connexion et permission CONNECT absente. L'erreur 18456 seule n'est pas un diagnostic complet. Utilisez ses détails serveur et une véritable tentative de connexion.

Réassocier l'utilisateur existant

Si le bon login cible existe déjà, modifiez l'association sur place.

-- Run in the restored database only after verifying the intended mapping.
ALTER USER [AppUser] WITH LOGIN = [AppLogin];

AppUser et AppLogin sont des noms illustratifs à remplacer par la paire vérifiée. ALTER USER conserve le principal de base existant au lieu de le supprimer et le recréer. Ses permissions et relations de rôle peuvent ainsi rester attachées.

Supprimer l'utilisateur peut perdre des droits explicites ou échouer parce qu'il possède des schémas. Le recréer sous le même nom ne rétablit pas automatiquement toutes ses relations. Ne transformez pas une réparation d'association en refonte involontaire des accès.

Si le login doit réellement être recréé, décidez de préserver ou non le SID initial par une procédure de migration approuvée. Cela peut conserver les associations de plusieurs bases restaurées. Gérez secrets et politiques de compte par le processus sécurisé habituel, sans copier des mots de passe dans un script improvisé.

Évitez une réassociation automatique de tous les utilisateurs sur le seul nom. Vérifiez une liste concrète d'identités source et cible, surtout lors d'une consolidation d'instances. Des noms égaux peuvent masquer des frontières de confiance différentes.

Valider identité et capacité

Confirmez le lien des SID et les rôles attendus.

SELECT dp.name AS DatabaseUser, sp.name AS MappedLogin,
       dp.sid AS DatabaseUserSid, sp.sid AS LoginSid
FROM sys.database_principals AS dp
JOIN sys.server_principals AS sp ON sp.sid = dp.sid
WHERE dp.name = N'AppUser';

SELECT r.name AS DatabaseRole
FROM sys.database_role_members AS drm
JOIN sys.database_principals AS r ON r.principal_id = drm.role_principal_id
JOIN sys.database_principals AS m ON m.principal_id = drm.member_principal_id
WHERE m.name = N'AppUser';

La liste des rôles n'est qu'une partie de l'inventaire. Droits directs, permissions de schéma, DENY et propriété peuvent aussi intervenir. Comparez les droits pertinents avant et après plutôt que de supposer les rôles exhaustifs.

Ouvrez ensuite une nouvelle connexion avec le vrai login applicatif et la base initiale prévue. Exécutez une opération autorisée et vérifiez qu'une opération extérieure au rôle reste refusée. Un essai uniquement sous sysadmin contourne précisément le chemin réparé.

Pour un groupe de disponibilité, préparez les logins requis sur chaque réplica susceptible de devenir primaire. Le fonctionnement du primaire actuel ne prouve pas celui d'un basculement futur. Intégrez ces contrôles aux exercices de restauration et de basculement.

Les utilisateurs contenus peuvent réduire la dépendance aux logins d'instance lorsque ce modèle convient. Leur adoption reste une décision distincte sur l'authentification et les connexions, pas un correctif d'urgence universel.

Conservez l'association vérifiée et les résultats avec le dossier de migration. Une restauration terminée doit signifier que la bonne identité peut accomplir son travail tout en conservant ses limites d'accès.

Références techniques: Microsoft Learn: Troubleshoot orphaned users · Microsoft Learn: ALTER USER.