Contexte
L’une des tâches régulièrement exécutées par un DBA est de restaurer ou d’attacher des bases entres différentes instances SQL. Lors de ce type d’opération, il est fréquent de rencontrer des erreurs d’authentification du type :
- Error 15023 : User already exists in the current database,
- Error 4064 : Cannot open user default database. Login failed,
- The database <VOTREBASE> is not accessible. (Object Explorer). A la vue de ces erreurs, un DBA peux être tenté de retirer manuellement les utilisateurs des différentes bases de données pour les ajouter, cette action – bien que fastidieuse – résoudrais le problème décrit, il existe néanmoins des solutions intégrées à SQL pour détecter et mapper les utilisateurs orphelins (orphaned users).
Explication du problème
Les utilisateurs orphelins apparaissent lorsque le SID d’un utilisateur d’une base de donnée ne correspond pas au SID du même utilisateur qui est renseigné dans la base master.
Le SID d’un utilisateur apparait donc à deux niveaux :
- Au niveau instance : dans les vues système '”sys.server_principals” et “sys.syslogins” présentes dans la table Master,
- Au niveau base de donnée : dans la table système sysusers.
Le SID d’un utilisateur (identifiant unique) sert à mapper un utilisateur d’une base de donnée au login renseigné dans l’instance SQL, donc lors de la restauration d’une base de donnée vers une instance SQL différente, il faut penser à re-mapper les utilisateurs orphelins.
Résolution
Afin de re-mapper les utilisateurs orphelins sans avoir à passer sur chaque utilisateur manuellement, on peux s’aider de sp_change_users, cette commande prends trois paramètres différents :
- Report pour détecter les utilisateurs orphelins :
| USE <YOURDATABASE>
GO
sp_change_users_login @Action='Report'
GO |
- Update_one pour mapper manuellement un utilisateur de la base à un login de l’instance :
USE <YOURDATABASE>
GO
sp_change_users_login @Action='update_one'
@UserNamePattern='User1'
@LoginName='User1'
GO |
- Auto_fix pour mapper automatiquement un utilisateur de la base à un login de l’instance et pour créer le login si absent de l’instance :
USE <YOURDATABASE>
GO
sp_change_users_login @Action='auto_fix' , 'User1', null, 'Password'
GO |