Dépanner des utilisateurs orphelins (SQL Server)

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

L’apparition d’utilisateurs orphelins dans SQL Server se produit lorsqu'un utilisateur de base de données est basé sur un utilisateur dans la base de données MASTER, mais que la session n’existe plus dans le MASTER. Cela peut se produire lorsque l’utilisateur est supprimé, ou lorsque la base de données est déplacée vers un autre serveur sur lequel l’utilisateur n'existe pas. Cette rubrique décrit comment rechercher des utilisateurs orphelins, puis comment les remapper à des utilisateurs.

Notes

Réduisez la possibilité d’apparition d’utilisateurs orphelins en utilisant des utilisateurs de base de données autonome pour les bases de données pouvant être déplacées. Pour plus d’informations, voir Utilisateurs de base de données autonome - Rendre votre base de données portable.

Arrière-plan

Pour connecter une base de données à une instance de SQL Server avec un principal de sécurité (identité de l’utilisateur de base de données) basé sur un utilisateur, le principal doit disposer d’un identifiant valide dans la base de données master . Cette connexion est utilisée dans le processus d'authentification chargé de vérifier l’identité du principal pour s’assurer que le principal est autorisé à se connecter à l'instance SQL Server. Les connexions SQL Server d’une instance de serveur sont visibles dans l’affichage catalogue sys.server_principals et l’affichage de compatibilité sys.syslogins .

Le compte de connexion SQL Server accède aux bases de données individuelles en tant qu’« utilisateur de base de données » mappé au compte de connexion SQL Server. Il y a trois exceptions à cette règle :

• Utilisateurs de base de données autonome

  Les utilisateurs de base de données autonome s’authentifient au niveau base de données d’utilisateurs et ne sont pas associés aux utilisateurs. Cela est recommandé, car les bases de données sont plus portables, et les utilisateurs de base de données autonome ne peuvent ainsi pas devenir orphelins. Cependant, ils doivent être recréés pour chaque base de données. Cela pourrait être peu pratique dans un environnement avec plusieurs bases de données.

• Le compte invité .

  Lorsqu'il est activé dans la base de données, ce compte autorise les connexions SQL Server non mappées à un utilisateur de base de données à accéder en tant qu' invité à la base de données. Le compte invité est désactivé par défaut.

• Appartenance aux groupes Microsoft Windows.

  Une connexion SQL Server créée à partir d'un utilisateur Windows peut accéder à la base de données si cet utilisateur est membre d'un groupe Windows lui-même utilisateur de la base de données.

Les informations relatives au mappage d'une connexion SQL Server à un utilisateur de base de données sont stockées dans la base de données. Elles incluent le nom de l'utilisateur de base de données et l'identificateur de sécurité de connexion (SID) de la connexion SQL Server correspondante. Les autorisations de cet utilisateur de base de données sont appliquées comme autorisation de la base de données.

Un utilisateur de base de données (basé sur un identifiant) pour lequel la connexion SQL Server correspondante n’est pas définie sur une instance serveur, ou l’est de façon incorrecte, ne peut pas se connecter à cette instance. L'utilisateur devient donc un utilisateur orphelin de la base de données sur cette instance du serveur. Le fait de se retrouver orphelin peut se produire si l'utilisateur de base de données est mappé à un SID de connexion absent dans l’instance master . Un utilisateur peut devenir orphelin après qu'une base de données a été restaurée ou attachée à une autre instance de SQL Server sur laquelle l’identifiant n’a jamais été créé. Un utilisateur de base de données peut également se retrouver orphelin si la connexion SQL Server correspondante est supprimée. Même si l’utilisateur est recréé, il aura un SID différent. Ainsi, l'utilisateur de base de données reste orphelin.

Détecter des utilisateurs orphelins

Pour SQL Server et PDW

Pour détecter des utilisateurs orphelins dans SQL Server en fonction des connexions d’authentification SQL Server manquantes, exécutez l’instruction suivante dans la base de données utilisateur :

SELECT dp.type_desc, dp.sid, dp.name AS user_name  
FROM sys.database_principals AS dp  
LEFT JOIN sys.server_principals AS sp  
    ON dp.sid = sp.sid  
WHERE sp.sid IS NULL  
    AND dp.authentication_type_desc = 'INSTANCE';  

Vous obtenez la liste des utilisateurs d’authentification SQL Server et de leurs identificateurs de sécurité (SID) correspondants qui, dans la base de données active, ne sont liés à aucun compte de connexion SQL Server.

Pour SQL Database et Azure Synapse Analytics

La table sys.server_principals n’est pas disponible dans SQL Database ou Azure Synapse Analytics. Pour identifier les utilisateurs orphelins dans ces environnements, procédez comme suit :

1. Connectez-vous à la base de données master et sélectionnez les SID pour les connexions avec la requête suivante :

   SELECT sid 
   FROM sys.sql_logins 
   WHERE type = 'S'; 
   

2. Connectez-vous à la base de données utilisateur et passez en revue les SID des utilisateurs dans la table sys.database_principals , à l’aide de la requête suivante :

   SELECT name, sid, principal_id
   FROM sys.database_principals 
   WHERE type = 'S' 
     AND name NOT IN ('guest', 'INFORMATION_SCHEMA', 'sys')
     AND authentication_type_desc = 'INSTANCE';
   

3. Comparez les deux listes pour déterminer si des SID dans la table sys.database_principals de la base de données utilisateur n’ont aucun SID de connexion correspondant dans la table sql_logins de la base de données master.

Résoudre le cas d'un utilisateur orphelin

Dans la base de données master, utilisez l’instruction CREATE LOGIN avec l’option SID pour recréer un identifiant manquant, en fournissant le SID de l’utilisateur de base de données obtenu dans la section précédente :

CREATE LOGIN <login_name>   
WITH PASSWORD = '<use_a_strong_password_here>',  
SID = <SID>;  

Pour mapper un utilisateur orphelin à un identifiant qui existe déjà dans master, exécutez l’instruction ALTER USER dans la base de données utilisateur en spécifiant le nom d’identifiant.

ALTER USER <user_name> WITH Login = <login_name>;  

Lorsque vous recréez un identifiant manquant, l’utilisateur peut accéder à la base de données avec le mot de passe fourni. L’utilisateur peut ensuite modifier le mot de passe du compte avec l’instruction ALTER LOGIN.

ALTER LOGIN <login_name> WITH PASSWORD = '<enterStrongPasswordHere>';  

Important

N’importe quel utilisateur peut modifier son propre mot de passe. Seuls les utilisateurs avec l’autorisation ALTER ANY LOGIN peuvent modifier le mot de passe d’un autre utilisateur. Toutefois, seuls les membres du rôle sysadmin peuvent modifier les mots de passe des membres du rôle sysadmin .

Voir aussi

CREATE LOGIN (Transact-SQL)
ALTER USER (Transact-SQL)
CRÉER UN UTILISATEUR (Transact-SQL)
sys.database_principals (Transact-SQL)
sys.server_principals (Transact-SQL)
sp_change_users_login (Transact-SQL)
sp_addlogin (Transact-SQL)
sp_grantlogin (Transact-SQL)
sp_password (Transact-SQL)
sys.sysusers (Transact-SQL)
sys.sql_loginssys.syslogins (Transact-SQL)