Gestion des utilisateurs orphelins dans SQL Server : Un guide complet

La gestion des utilisateurs orphelins dans SQL Server est essentielle pour la sécurité et l’administration des bases de données. Les utilisateurs orphelins se produisent lorsqu’un utilisateur de base de données existe sans un login correspondant dans la base de données principale. Ce déséquilibre peut perturber l’accès et entraîner des vulnérabilités potentielles en matière de sécurité. Dans cet article, nous explorerons ce que sont les utilisateurs orphelins, comment les détecter et les méthodes pour les résoudre, y compris des exemples et des scénarios mis à jour.

Contexte

Pour se connecter à une base de données SQL Server, un utilisateur doit avoir un login valide dans la base de données principale. Ce login authentifie l’utilisateur et est associé à un utilisateur de base de données pour l’autorisation. L’association est maintenue à l’aide de l’identifiant de sécurité (SID), garantissant que les utilisateurs de base de données et les logins serveur sont correctement liés.

Les exceptions à la règle de l’association login-utilisateur comprennent:

1. Utilisateurs de base de données contenus: S’authentifient au niveau de la base de données, éliminant la dépendance aux logins serveur. Ces utilisateurs améliorent la portabilité de la base de données mais nécessitent une recréation séparée pour chaque base de données.
2. Comptes invités: Activés par défaut, ils permettent l’accès aux utilisateurs sans association explicite mais sont généralement déconseillés pour des raisons de sécurité.
3. Membres de groupes Microsoft Windows: Les membres de groupes Windows peuvent accéder aux bases de données si le groupe est ajouté en tant qu’utilisateurs.

Un utilisateur de base de données devient orphelin lorsque:

• Le login correspondant est supprimé.
• La base de données est migrée ou restaurée sans les logins associés.
• La correspondance SID entre l’utilisateur et la connexion est incorrecte.

Qu’est-ce que les utilisateurs orphelins ?

Un utilisateur orphelin se produit lorsque :

• L’utilisateur de la base de données existe, mais la connexion SQL Server correspondante dans la base de données master n’existe pas.
• Le SID de l’utilisateur de la base de données et le SID de la connexion ne correspondent pas.

Ces scénarios perturbent l’authentification et l’autorisation, entraînant des erreurs ou un accès restreint.

Détection des utilisateurs orphelins

Pour SQL Server

Utilisez la requête suivante pour identifier les utilisateurs orphelins :

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

Pour Azure SQL Database ou Synapse Analytics

1. Récupérez les SIDs des connexions dans la base de données master :

SELECT sid 

FROM sys.sql_logins 

WHERE type = 'S'; 

2. Récupérez les SIDs des utilisateurs dans la base de données cible :

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 listes pour identifier les incohérences.

Résolution des utilisateurs orphelins

1. Recréer la connexion manquante avec le SID

Si la connexion a été supprimée :

CREATE LOGIN <login_name>   

WITH PASSWORD = '<use_a_strong_password_here>',  

SID = <SID>;

2. Mapper un utilisateur orphelin à une connexion existante

Si la connexion existe mais n’est pas mappée :

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

3. Changer le mot de passe de l’utilisateur

Après avoir recréé la connexion, mettez à jour son mot de passe :

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

Utilisation de sp_FindOrphanedUser

La procédure stockée sp_FindOrphanedUser est un outil puissant pour détecter et résoudre les utilisateurs orphelins. Elle fournit :

1. Une liste d’utilisateurs orphelins.
2. Des scripts générés automatiquement pour les réparations.
3. Détails sur les schémas appartenant à des utilisateurs orphelins.

Obtenez le code du package à partir de sp_FindOrphanedUser.

Exécution de la procédure

Exécution par défaut :

EXEC sp_FindOrphanedUser;

Pour une base de données spécifique :

EXEC sp_FindOrphanedUser @DatabaseName = '<DatabaseName>';

Exemples de scénarios d’utilisateurs orphelins

Exemple 1 : Utilisateur sans connexion

Scénario

Une connexion est supprimée, laissant l’utilisateur de la base de données orphelin.

• Détecter l’utilisateur orphelin:
  MS SQL

  EXEC sp_FindOrphanedUser;

• Reconstruire la connexion:
  MS SQL

  CRÉER LOGIN [User1] AVEC MOT DE PASSE = 'SecurePassword123';

  UTILISER [ExampleDB];

  MODIFIER UTILISATEUR [User1] AVEC LOGIN = [User1];

Exemple 2 : Incohérence de SID

Scénario

Le SID de connexion ne correspond pas au SID de l’utilisateur.

• Détecter la discordance:
  MS SQL

  EXEC sp_FindOrphanedUser @DatabaseName = 'ExampleDB';

• Corrigez la correspondance SID :
  MS SQL

  USE [ExampleDB];

  EXEC sp_change_users_login 'UPDATE_ONE', 'User2', 'User2';

Exemple 3 : Utilisateur orphelin possédant un schéma

Scénario

L’utilisateur orphelin empêche la suppression du schéma.

• Identifier la propriété du schéma :
  MS SQL

  EXEC sp_FindOrphanedUser @DatabaseName = 'ExampleDB';

• Réaffecter la propriété du schéma :
  MS SQL

  UTILISER [ExampleDB];

  MODIFIER L'AUTORISATION SUR LE SCHEMA::[User3Schema] À [dbo];

  ABANDONNER L'UTILISATEUR [User3];

Meilleures Pratiques

• Utiliser des Utilisateurs de Base de Données Contenus: Évitez la dépendance aux connexions de serveur.
• Synchroniser les Connexions: Toujours migrer ou restaurer les connexions lors des migrations de base de données.
• Audits Réguliers: Vérifiez périodiquement les utilisateurs orphelins pour maintenir la sécurité.

Conclusion

Gérer les utilisateurs orphelins garantit la sécurité de la base de données et l’efficacité opérationnelle. En utilisant des requêtes et des outils comme sp_FindOrphanedUser, vous pouvez rapidement identifier et résoudre les problèmes, maintenant un accès fluide pour les utilisateurs légitimes. L’adoption de meilleures pratiques telles que les utilisateurs contenus et les audits réguliers peut prévenir de tels problèmes à l’avenir.