Quick Memo MSSQL : logins et users orphelins

1. Identifier les users orphelins (sans login SQL  associé) :

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 authentication_type_desc = 'INSTANCE';

2. Résoudre automatiquement le problème, en créant le login SQL si nécessaire :

USE [database_name]
GO
EXEC sp_change_users_login 'Auto_Fix', 'My_User', NULL, 'My_Password';

Dans la commande T-SQL ci-dessus, remplacer [database_name] par le nom de la DB contenant l’utilisateur orphelin, ainsi que My_User et My_Password par le nom d’utilisateur et le mot de passe voulu pour le login SQL qui sera créé si nécessaire.

Cas pratique pour tester ces 2 scripts T-SQL :

1. Créer une DB test, par exemple Test01 ;
2. Créer un login test, par exemple Test01_User ;
3. Assigner le role db_owner sur la DB Test01 pour le login Test01_User (le user Test01_User est créé dans la DB à ce moment) ;
4. Prendre un backup FULL de la DB Test01 ;
5. Supprimer la DB Test01 et puis le login Test01_User ;
6. Restaurer la DB Test01 ;
7. Constater que le user Test01 est bien présent dans la DB, sous Test01 > Security > Users, mais qu’il n’y a plus de SQL login portant ce nom sous Security > Logins ;
8. Utiliser le script T-SQL repris au point 1 de ce post pour confirmer qu’il y a bien un user orphelin ;
9. Utiliser le script T-SQL repirs au point 2 de ce post, en adaptant les valeurs, pour créer le login SQL et le mapper au user SQL existant dans la DB ;
10. Vérifier que tout est rentré dans l’ordre, éventuellement en testant le login dans SSMS.