Dépanner des utilisateurs orphelins (SQL Server)

 

S’applique à : SQL Server 2016

CETTE RUBRIQUE S’APPLIQUE À : ouiSQL Server (à partir de la version 2008)ouiAzure SQL DatabaseouiAzure SQL Data WarehouseouiParallel Data Warehouse

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 l’utilisateur 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.

System_CAPS_ICON_note.jpg Remarque


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. Pour plus d’informations, consultez Utilisateurs de base de données à relation contenant-contenu - Rendre votre base de données portable.

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.

SQL Server accèdent aux bases de données en tant qu’utilisateur de base de données mappé à l’utilisateur SQL Server . Il y a trois exceptions à cette règle :

  • Les utilisateurs de base de données à relation contenant-contenu

    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. 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. 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.

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

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

Pour SQL Database et SQL Data Warehouse

La table sys.server_principals n’est pas disponible dans la SQL Database ou SQL Data Warehouse. 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.

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>';  

System_CAPS_ICON_important.jpg 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 .

La procédure déconseillée sp_change_users_login fonctionne également avec les utilisateurs orphelins. sp_change_users_login avec Base de données SQL.

CREATE LOGIN (Transact-SQL)
ALTER USER (Transact-SQL)
CREATE USER (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)

Ajouts de la communauté

AJOUTER
Afficher: