Partager via

Dépanner des utilisateurs orphelins (SQL Server)

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Les utilisateurs sont orphelins dans SQL Server lorsqu’un utilisateur de base de données est basé sur une connexion dans la master base de données, mais que la connexion n’existe plus dans master. Cela peut se produire lorsque la connexion est supprimée ou lorsque la base de données est déplacée vers un autre serveur sur lequel la connexion n’existe pas. Cet article explique comment rechercher des utilisateurs orphelins et les mappper à des connexions.

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 Rendre votre base de données portable en utilisation des bases de données autonomes.

Arrière-plan

Pour les connexions à une base de données sur une instance de SQL Server qui utilise un principal de sécurité (identité utilisateur de base de données) basée sur une connexion, le principal doit disposer d’une connexion valide dans la master base de données. Cette connexion est utilisée dans le processus d’authentification qui vérifie l’identité du principal et détermine si le principal est autorisé à se connecter à l’instance de 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 .

Les connexions SQL Server accèdent à des bases de données individuelles en tant qu'« utilisateur de base de données » mappés à la 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 de la base de données utilisateur et ne sont pas associés aux connexions. Ce modèle est recommandé, car les bases de données sont plus portables et les utilisateurs de base de données autonome ne peuvent pas devenir orphelins. Toutefois, ils doivent être recréé pour chaque base de données. Ce modèle peut être impraticable dans un environnement qui a de nombreuses bases de données.

  • Compte invité

    Lorsqu’il est activé dans une base de données, ce compte autorise les connexions SQL Server qui ne sont pas mappées à un utilisateur de base de données pour accéder à la base de données en tant qu’utilisateur invité . Le compte invité est désactivé par défaut.

  • Appartenances aux groupes Microsoft Windows

    Une connexion SQL Server créée à partir d’un utilisateur Windows peut accéder à une base de données si l’utilisateur Windows est membre d’un groupe Windows qui est également un utilisateur de la base de données.

Les informations sur le mappage d’une connexion SQL Server à un utilisateur de base de données sont stockées dans la base de données. Il inclut le nom de l’utilisateur de base de données et l’identificateur de sécurité (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 une connexion) pour lequel la connexion SQL Server correspondante n’est pas définie ou est incorrectement définie sur une instance de serveur ne peut pas se connecter à l’instance. L'utilisateur devient donc un utilisateur orphelin de la base de données sur cette instance du serveur. L’orphelin peut se produire si l’utilisateur de la base de données est mappé à une connexion SID qui n’est pas présente dans la master base de données. 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 la connexion est recréée, elle aura une autre SIDvaleur. Par conséquent, l’utilisateur de la base de données sera toujours orphelin.

Détecter les utilisateurs orphelins

Pour SQL Server et PDW

Pour détecter les 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';

La sortie répertorie les utilisateurs d’authentification SQL Server et les SID correspondants dans la base de données active qui ne sont liés à aucune connexion SQL Server.

Pour Azure SQL Database et Azure Synapse Analytics

La table sys.server_principals n’est pas disponible dans SQL Database ou Azure Synapse Analytics. Identifiez les utilisateurs orphelins dans ces environnements en effectuant les étapes suivantes :

  1. Connectez-vous à la master base de données et sélectionnez les SID pour les connexions à l’aide de 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 de la sys.database_principals table à 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 s’il existe des SID utilisateur dans la table de base de données sys.database_principals utilisateur qui ne sont pas mises en correspondance par les SID de connexion dans la master table de base de données sql_logins .

Résoudre un utilisateur orphelin

Dans la master base de données, utilisez l’instruction CREATE LOGIN avec l’option SID permettant de recréer une connexion manquante. Indiquez l’utilisateur SID de base de données que vous avez 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 à une connexion qui existe déjà dans master, exécutez l’instruction ALTER USER dans la base de données utilisateur, en spécifiant le nom de connexion :

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

Lorsque vous recréez une connexion manquante, l’utilisateur peut accéder à la base de données à l’aide du mot de passe fourni. L’utilisateur peut ensuite modifier le mot de passe du compte de connexion à l’aide de 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 .

Contenu connexe

  • Last updated on