Vous avez déjà sans doute eu le cas où un administrateur système par mégarde, a supprimé un compte Windows utilisé par votre serveur SQL et qui est mappé à un ou plusieurs utilisateurs de vos bases de données . Vous avez sans doute maudit ce pauvre et malchanceux administrateur système car le problème dans ce cas est que le nouveau compte Windows recréé avec les mêmes paramètres ne possèdent pas le même SID et que le mappage avec les différents utilisateurs de bases de données existants est rompu. La question évidente ici est comment allons nous faire pour pouvoir revenir à une situation normale ?

Reproduisons tout d’abord notre problème en créant tout d’abord un utilisateur Windows test  dans une fenêtre de commande :

> net user test test /ADD

Cette commande créé un utilisateur test avec un mot de passe test. Ensuite sur le serveur SQL créons un compte de connexion relatif à ce compte Windows :

USE master;
GO

CREATE LOGIN « WIN-4SPCKAEGSXA\test » FROM WINDOWS;
GO

Sur une base « bdd_test » créons maintenant un utilisateur associé à ce compte de connexion et donnons lui les droits adéquates pour lire les données de la table :

USE bdd_test;
GO

РCr̩ation user
CREATE USER « WIN-4SPCKAEGSXA\test » FROM LOGIN « WIN-4SPCKAEGSXA\test »;
GO
– Droits octroyés pour l’utilisateur WIN-4SPCKAEGSXA\test
GRANT SELECT ON DATABASE::bdd_test TO « WIN-4SPCKAEGSXA\test »;

Pour un compte de connexion de type SQL Server un SID (Security IDentifier) est généré automatiquement par le moteur au moment de la création de ce compte alors que pour un compte de connexion de type Windows le moteur récupère le SID généré par le système d’exploitation. Un utilisateur de base de données est mappé à ce compte de connexion par l’intermédiaire de ce SID. Nous pouvons le vérifier à l’aide de la requête suivante :

SELECT
    p.name AS login_name,
    p.sid,
    u.name AS [user_name],
    u.sid
FROM sys.server_principals p
INNER JOIN sys.sysusers u
ON p.sid = u.sid
WHERE p.name = ‘WIN-4SPCKAEGSXA\test';

– SID : 0x010500000000000515000000D2F218682240AB876668EF59EF030000

qui donne le résultat suivant :

Nous nous trouvons à présent dans la situation où tout fonctionne parfaitement. Maintenant passons à la 2ème phase où notre administrateur supprime par mégarde l’utilisateur test. Nous pouvons le simuler ici par la commande dos suivante :

> net user test /DELETE

Notre administrateur va donc recréer ce compte d’utilisateur. Cependant la création d’un nouveau compte Windows génère un nouvel SID .

> net user test test /ADD

. ce qui explique que notre compte de connexion SQL Server associé n’est plus valide. Une tentative de connexion donnera le message suivant :

Recréons notre compte de connexion SQL Server :

DROP LOGIN « WIN-4SPCKAEGSXA\test »;
GO

CREATE LOGIN « WIN-4SPCKAEGSXA\test » FROM WINDOWS;
GO

et vérifions maintenant le SID associé :

SELECT
    name AS login_name,
    sid
FROM sys.server_principals
WHERE name = ‘WIN-4SPCKAEGSXA\test';

 

On constate bien que le le SID a changé : 0x01…59EF030000 au lieu 0x01…59F030000 .

La connexion au serveur SQL fonctionne à nouveau mais l’accès à la base de données bdd_test est refusé :

 

Pourquoi ? Tout simplement car aucun utilisateur est mappé à ce nouveau compte de connexion pour le moment. Pour le vérifier il suffit de reprendre la requête d’interrogation des vues sys.server_principals et sys.sysusers et en changeant le type de jointure en jointure externe :

SELECT
    p.name AS login_name,
    p.sid,
    u.name AS [user_name],
    u.sid
FROM sys.server_principals p
LEFT JOIN sys.sysusers u
ON p.sid = u.sid
WHERE p.name = ‘WIN-4SPCKAEGSXA\test';

qui donne :

Hors, il existe bien un utilisateur de base de données ‘WIN-4SPCKAEGSXA\test’ mais celui-ci est devenu orphelin car le mappage n’existe plus entre le nouveau compte de connexion SQL Server et cet utilisateur. Les SID ne correspondent plus.

Il existe une procédure stockée sp_change_users_login qui permet de résoudre ce problème en remappant les utilisateurs de bases de données orphelins aux comptes de connexions mais celle-ci ne fonctionne que pour les types de connexion SQL Server et non Windows.

Une solution rapide serait dans notre cas de supprimer l’utilisateur de bases de données et de le recréer mais ceci implique qu’il faudrait également paramétrer à nouveau les différents droits associés. Ceci peut être contraignant si pour un compte de connexion il existe une multitude d’utilisateurs dans différentes bases de données.

Une autre solution plus adéquate est d’utiliser la commande ALTER USER qui permettra de régénérer le SID de notre utilisateur en fonction du SID du nouveau compte de connexion recréé.

USE bdd_test;
GO

ALTER USER « WIN-4SPCKAEGSXA\test » WITH LOGIN = « WIN-4SPCKAEGSXA\test »;

Vérifions que le mappage s’est déroulé correctement :

SELECT
    p.name AS login_name,
    p.sid,
    u.name AS [user_name],
    u.sid
FROM sys.server_principals p
INNER JOIN sys.sysusers u
ON p.sid = u.sid
WHERE p.name = ‘WIN-4SPCKAEGSXA\test';

et qui donne :

Le mappage est de nouveau fonctionnel entre notre compte de connexion et notre utilisateur de bases de données !!

Bon remappage !!

David BARBARIN (Mikedavem)
Elève ingénieur CNAM Lyon