Trouver et rattacher les utilisateurs orphelins SQL Server
Requêtes pour repérer les utilisateurs de base orphelins, ceux liés à un certificat expiré, et commandes pour les rattacher à un login.
Un utilisateur orphelin est un utilisateur de base de données dont le SID ne correspond à aucun login du serveur, par exemple après la restauration d'une base sur un autre serveur. La démarche suivante suit l'article Different ways to find SQL Server orphaned users.
Trouver les utilisateurs orphelins
Exécuter la requête dans la base concernée. Elle exclut les utilisateurs système et les utilisateurs sans login qui ont un SID de 28 octets (ceux-ci sont valides).
-- find orphaned users from windows/certificate/asymmetric_key login
select dp.name, dp.type, dp.sid, LEN(dp.sid) as [SID_Len]
from sys.database_principals dp
left join sys.server_principals sp
on dp.sid = sp.sid
left join sys.certificates c
on dp.sid = c.sid
left join sys.asymmetric_keys a
on dp.sid = a.sid
where sp.sid is null and c.sid is null and a.sid is null
-- check dp.type, go to the following
--https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-principals-transact-sql
and dp.type in ('U', 'S', 'C', 'K')
and dp.principal_id > 4 -- 0..4 are system users which will be ignored
and not (dp.type = 'S' and LEN(dp.sid) = 28) -- to filter out the valid db users without login
Utilisateurs liés à un certificat expiré
select [DBUser]= dp.name
, [certificate]=mc.name
, mc.expiry_date
, curr_date = GETDATE()
from sys.database_principals dp
inner join master.sys.certificates mc
on dp.sid = mc.sid
where mc.expiry_date < getdate();
Rattacher un utilisateur à un login
Lister les utilisateurs non rattachés, puis rattacher ceux dont le nom correspond à un login existant :
EXEC sp_change_users_login 'Report';
EXEC sp_change_users_login 'auto_fix', 'app_user';
Vérifier un login Windows
exec xp_logininfo 'DOMAIN\user';
Remarques
sp_change_users_loginest déprécié. Pour rattacher un utilisateur à un login, la syntaxe recommandée estALTER USER ... WITH LOGIN.auto_fixrattache l'utilisateur au login de même nom. Vérifier le résultat deReportavant de l'exécuter.