Résoudre les problèmes liés aux utilisateurs orphelins (SQL Server)Troubleshoot Orphaned Users (SQL Server)

CETTE RUBRIQUE S’APPLIQUE À : ouiSQL Server (à partir de la version 2008)ouiAzure SQL DatabaseouiAzure SQL Data WarehouseouiParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

L’apparition d’utilisateurs orphelins dans SQL ServerSQL 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 l’utilisateur n’existe plus dans le MASTER.Orphaned users in SQL ServerSQL Server occur when a database user is based on a login in the master database, but the login no longer exists in 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.This can occur when the login is deleted, or when the database is moved to another server where the login does not exist. Cette rubrique décrit comment rechercher des utilisateurs orphelins, puis comment les remapper à des utilisateurs.This topic describes how to find orphaned users, and remap them to logins.

Note

Réduisez la possibilité d’apparition d’utilisateurs orphelins en utilisant des utilisateurs de base de données à relation contenant-contenu pour les bases de données pouvant être déplacées.Reduce the possibility of orphaned users by using contained database users for databases that might be moved. Pour plus d’informations, consultez Utilisateurs de base de données à relation contenant-contenu - Rendre votre base de données portable.For more information, see Contained Database Users - Making Your Database Portable.

Arrière-planBackground

Pour connecter une base de données à une instance de SQL ServerSQL 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 .To connect to a database on an instance of SQL ServerSQL Server using a security principal (database user identity) based on a login, the principal must have a valid login in the master database. 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 ServerSQL Server.This login is used in the authentication process that verifies the principals identity and determines if the principal is allowed to connect to the instance of SQL ServerSQL Server. Les connexions SQL ServerSQL Server d’une instance de serveur sont visibles dans l’affichage catalogue sys.server_principals et l’affichage de compatibilité sys.syslogins .The SQL ServerSQL Server logins on a server instance are visible in the sys.server_principals catalog view and the sys.sql_logins compatibility view.

SQL ServerSQL Server accèdent aux bases de données en tant qu’utilisateur de base de données mappé à l’utilisateur SQL ServerSQL Server . login. Il y a trois exceptions à cette règle :There are three exceptions to this rule:

  • Les utilisateurs de base de données à relation contenant-contenuContained database users

    Les utilisateurs de base de données à relation contenant-contenu s’authentifient au niveau base de données d’utilisateurs et ne sont pas associés aux utilisateurs.Contained database users authenticate at the user-database level and are not associated with logins. Cela est recommandé, car les bases de données sont plus portables, et les utilisateurs de base de données à relation contenant-contenu ne peuvent ainsi pas devenir orphelins.This is recommended because the databases are more portable and contained database users cannot become orphaned. Cependant, ils doivent être recréés pour chaque base de données.However they must be recreated for each database. Cela pourrait être peu pratique dans un environnement avec plusieurs bases de données.This might be impractical in an environment with many databases.

  • Le compte invité .The guest account.

    Lorsqu'il est activé dans la base de données, ce compte autorise les connexions SQL ServerSQL Server non mappées à un utilisateur de base de données à accéder en tant qu' invité à la base de données.When enabled in the database, this account permits SQL ServerSQL Server logins that are not mapped to a database user to enter the database as the guest user. Le compte invité est désactivé par défaut.The guest account is disabled by default.

  • Appartenance aux groupes Microsoft Windows.Microsoft Windows group memberships.

    Une connexion SQL ServerSQL 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.A SQL ServerSQL Server login created from a Windows user can enter a database if the Windows user is a member of a Windows group that is also a user in the database.

    Les informations relatives au mappage d'une connexion SQL ServerSQL Server à un utilisateur de base de données sont stockées dans la base de données.Information about the mapping of a SQL ServerSQL Server login to a database user is stored within the database. 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 ServerSQL Server correspondante.It includes the name of the database user and the SID of the corresponding SQL ServerSQL Server login. Les autorisations de cet utilisateur de base de données sont appliquées comme autorisation de la base de données.The permissions of this database user are applied for authorization in the database.

    Un utilisateur de base de données (basé sur un identifiant) pour lequel la connexion SQL ServerSQL 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.A database user (based on a login) for which the corresponding SQL ServerSQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance. L'utilisateur devient donc un utilisateur orphelin de la base de données sur cette instance du serveur.Such a user is said to be an orphaned user of the database on that server instance. 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 .Orphaning can happen if the database user is mapped to a login SID that is not present in the master instance. 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 ServerSQL Server sur laquelle l’identifiant n’a jamais été créé.A database user can become orphaned after a database is restored or attached to a different instance of SQL ServerSQL Server where the login was never created. Un utilisateur de base de données peut également se retrouver orphelin si la connexion SQL ServerSQL Server correspondante est supprimée.A database user can also become orphaned if the corresponding SQL ServerSQL Server login is dropped. Même si l’utilisateur est recréé, il aura un SID différent. Ainsi, l'utilisateur de base de données reste orphelin.Even if the login is recreated, it will have a different SID, so the database user will still be orphaned.

Pour détecter des utilisateurs orphelinsTo Detect Orphaned Users

Pour SQL ServerSQL Server et PDWFor SQL ServerSQL Server and PDW

Pour détecter des utilisateurs orphelins dans SQL ServerSQL Server en fonction des connexions d’authentification SQL ServerSQL Server manquantes, exécutez l’instruction suivante dans la base de données utilisateur :To detect orphaned users in SQL ServerSQL Server based on missing SQL ServerSQL Server authentication logins, execute the following statement in the user database:

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 authentication_type_desc = 'INSTANCE';  

Vous obtenez la liste des utilisateurs d’authentification SQL ServerSQL Server et de leurs identificateurs de sécurité (SID) qui, dans la base de données active, ne sont liés à aucune connexion SQL ServerSQL Server .The output lists the SQL ServerSQL Server authentication users and corresponding security identifiers (SID) in the current database that are not linked to any SQL ServerSQL Server login.

Pour SQL Database et SQL Data WarehouseFor SQL Database and SQL Data Warehouse

La table sys.server_principals n’est pas disponible dans la SQL Database ou SQL Data Warehouse.The sys.server_principals table is not available in SQL Database or SQL Data Warehouse. Pour identifier les utilisateurs orphelins dans ces environnements, procédez comme suit :Identify orphaned users in those environments with the following steps:

  1. Connectez-vous à la base de données master et sélectionnez les SID pour les connexions avec la requête suivante :Connect to the master database and select the SID's for the logins with the following query:

    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 :Connect to the user database and review the SID's of the users in the sys.database_principals table, by using the following query:

    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.Compare the two lists to determine if there are user SID's in the user database sys.database_principals table which are not matched by login SID's in the master database sql_logins table.

Pour résoudre le cas d'un utilisateur orphelinTo Resolve an Orphaned User

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 :In the master database, use the CREATE LOGIN statement with the SID option to recreate a missing login, providing the SID of the database user obtained in the previous section:

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.To map an orphaned user to a login which already exists in master, execute the ALTER USER statement in the user database, specifying the login name.

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.When you recreate a missing login, the user can access the database using the password provided. L’utilisateur peut ensuite modifier le mot de passe du compte avec l’instruction ALTER LOGIN.Then the user can alter the password of the login account by using the ALTER LOGIN statement.

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

N’importe quel utilisateur peut modifier son propre mot de passe.Any login can change it's own password. Seuls les utilisateurs avec l’autorisation ALTER ANY LOGIN peuvent modifier le mot de passe d’un autre utilisateur.Only logins with the ALTER ANY LOGIN permission can change the password of another user's login. Toutefois, seuls les membres du rôle sysadmin peuvent modifier les mots de passe des membres du rôle sysadmin .However, only members of the sysadmin role can modify passwords of sysadmin role members.

La procédure déconseillée sp_change_users_login fonctionne également avec les utilisateurs orphelins.The deprecated procedure sp_change_users_login also works with orphaned users. sp_change_users_login avec Base de données SQLSQL Database.sp_change_users_login cannot be used with Base de données SQLSQL Database.

Voir aussiSee Also

CREATE LOGIN (Transact-SQL) CREATE LOGIN (Transact-SQL)
ALTER USER (Transact-SQL) ALTER USER (Transact-SQL)
CREATE USER (Transact-SQL) CREATE USER (Transact-SQL)
sys.database_principals (Transact-SQL) sys.database_principals (Transact-SQL)
sys.server_principals (Transact-SQL) sys.server_principals (Transact-SQL)
sp_change_users_login (Transact-SQL) sp_change_users_login (Transact-SQL)
sp_addlogin (Transact-SQL) sp_addlogin (Transact-SQL)
sp_grantlogin (Transact-SQL) sp_grantlogin (Transact-SQL)
sp_password (Transact-SQL) sp_password (Transact-SQL)
sys.sysusers (Transact-SQL) sys.sysusers (Transact-SQL)
sys.sql_logins sys.syslogins (Transact-SQL) sys.sql_logins sys.syslogins (Transact-SQL)