Bonjour à tous, c'est AlexIn Tech! Aujourd'hui, je vais partager avec vous une astuce technique que j'ai récemment utilisée lors de la migration d'un cluster SQL. J'avais besoin de vérifier l'existence d'"orphaned users" après avoir restauré ma base de données sur un nouveau cluster. Pour cela j'ai écrit un petit script TSQL et je vais vous l'expliquer en détail.
Qu'est-ce qu'un "Orphaned User" dans SQL Server?
Dans SQL Server, un "orphaned user" est un utilisateur de base de données qui n'est plus associé à une connexion valide au niveau de l'instance. Cela arrive souvent après la restauration d'une base de données dans un nouvel environnement.
Le Script pour Détecter les "Orphaned Users"
Voici mon petit script, il marche super.
-- Use your database
USE [YourDatabaseName];
GO
-- Create a temporary table to store user information
CREATE TABLE #DBUsers
(
DBUserName NVARCHAR(128),
DBUserSID VARBINARY(85)
);
-- Insert database user information into the temp table
INSERT INTO #DBUsers (DBUserName, DBUserSID)
SELECT name, sid
FROM sys.database_principals
WHERE type_desc IN ('SQL_USER', 'WINDOWS_USER', 'WINDOWS_GROUP');
-- Select and compare with instance logins
SELECT
u.DBUserName AS DatabaseUser,
u.DBUserSID AS DatabaseUserSID,
p.name AS InstanceLogin,
p.sid AS InstanceLoginSID,
CASE
WHEN p.sid IS NULL THEN 'Orphaned User'
ELSE 'Matched'
END AS Status
FROM
#DBUsers u
LEFT JOIN
sys.server_principals p ON u.DBUserSID = p.sid;
-- Drop the temporary table
DROP TABLE #DBUsers;
Explication du Script
Sélection de la Base de Données : Remplacez [YourDatabaseName] par le nom de votre base de données pour cibler celle-ci.
Création d'une Table Temporaire : Le script commence par créer une table temporaire #DBUsers. En TSQL, le préfixe # indique une table temporaire qui existe uniquement pendant la durée de la session et est stockée dans tempdb.*(lire la nota bene en bas d'articles pour plus d'infos)
Insertion des Utilisateurs : Ensuite, il insère les noms et SID (Security Identifier) des utilisateurs de la base de données dans cette table temporaire.
Comparaison avec les Connexions de l'Instance : La requête principale compare les SID des utilisateurs de la base de données avec ceux des connexions de l'instance pour identifier les "orphaned users".
Nettoyage : Enfin, la table temporaire est supprimée.
Comment Utiliser le Script?
Remplacez le Nom de la Base de Données : Commencez par remplacer [YourDatabaseName] par le nom de votre base de données.
Exécutez le Script : Lancez le script dans votre environnement SQL Server Management Studio (SSMS).
Analysez les Résultats : Les utilisateurs marqués 'Orphaned User' sont ceux que vous devez examiner.
Actions : Vous pouvez alors choisir de les supprimer ou de les relier à de nouvelles connexions.
Comprendre les Faux-Positifs parmi les "Orphaned Users"
Après avoir parcouru comment utiliser le script pour détecter les "orphaned users", il est important de comprendre que, parfois, identifier un "orphaned user" ne signale pas forcément un problème. Dans certains cas, ces utilisateurs orphelins peuvent être des faux-positifs. Voici pourquoi :
Utilisateurs Supprimés de l'Active Directory (AD) : Il est courant après un restore de DB de trouver des utilisateurs qui existaient dans l'AD lors de la création de la base de données mais qui ont depuis été supprimés de l'AD. Ces comptes, bien que toujours présents dans la base de données comme utilisateurs, ne sont plus dans les logins de l'instance car lorsque les logins de l'instance ont été restaurés ils n'existaient plus dans l'AD. Cela génère donc des "orphaned users" car ils sont des les users de la DB mais pas dans les logins de l'instance. Assez souvent des vieilleries qui n'avaient pas été supprimées.
Utilisateurs Spécifiques à la Base de Données : Certaines bases de données ont des utilisateurs créés spécifiquement pour elles, sans correspondance au niveau de l'instance. Ces utilisateurs sont souvent conçus pour des rôles ou des fonctions spécifiques à la base de données et n'ont pas besoin de connexion au niveau de l'instance.
J'espère que ce guide vous aidera à gérer efficacement les utilisateurs dans vos bases de données SQL Server. Les "orphaned users" peuvent être un casse-tête, mais avec le bon outil, ils sont faciles à repérer et à gérer.
Enjoy 😎
AlexIn Tech
*Nota Bene : Pourquoi supprimer explicitement une table temporaire ?
Raisons de Supprimer Explicitement une Table Temporaire
Clarté et Bonnes Pratiques : L'inclusion d'une commande DROP TABLE pour les tables temporaires est souvent une question de clarté et de bonnes pratiques de développement. Elle indique clairement aux personnes qui lisent le script que la table n'est plus nécessaire et que son cycle de vie est géré de manière proactive.
Gestion des Ressources : Bien que SQL Server gère automatiquement la suppression des tables temporaires, le fait de les supprimer explicitement dès qu'elles ne sont plus nécessaires peut aider à libérer des ressources, comme la mémoire et l'espace disque dans tempdb, plus rapidement, surtout dans de longues sessions ou transactions.
Prévention des Conflits dans de Longues Sessions : Dans des sessions particulièrement longues ou complexes, où plusieurs opérations sont effectuées, supprimer explicitement une table temporaire dès qu'elle n'est plus utile peut aider à prévenir des conflits ou des confusions avec d'autres parties du script qui pourraient utiliser des tables temporaires avec des noms similaires.
Habitude et Portabilité : Pour les développeurs qui travaillent avec différents systèmes de gestion de base de données (SGBD), l'habitude de supprimer explicitement toutes les tables, y compris les temporaires, peut être une pratique courante. Cela assure une certaine uniformité dans la gestion des ressources, indépendamment des spécificités de chaque SGBD.