User Orphelins

Après l'attachement ou la restauration d'une base de données, les utilisateurs et les connexions précédemment créés dans cette base de données ne correspondent pas et sont considérés comme des utilisateurs orphelins. Les symptômes les plus courants sont les suivants:

• l'application peut rencontrer des erreurs d'échec de connexion.
• un message indiquant que l'utilisateur existe déjà dans la base de données actuelle lorsque vous essayez d'ajouter l'identifiant de connexion à la base de données.

Méthode 1

Cette méthode fonctionne toujours jusqu'à MSSQL 2025, mais sera deprecié dans les prochaines release.

• Identifier les Orphelins

USE adventureWorks
GO
sp_change_users_login @Action='Report'
GO

• corriger les orphelins

USE AdventureWorks
GO
 
sp_change_users_login @Action='update_one',
@UserNamePattern='TestUser1',
@LoginName='TestUser1'
GO

Méthode 2

• Identifier les Orphelins

 -- 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;

• corriger les orphelins si le Login existe déjà

 alter user 'robert' with login 'robert'

• ou créer le Login en forçant le SID du user de BDD

-- Lister le SID du user de BDD1211
use [DB1]
GO
select name,type_desc,create_date,sid from sys.database_principals
 
 
-- SQL LOGIN
create login [robert]
with password 'xxxxxx', sid='xxxxx'
 
-- AD USER/GROUP
create login [AD\robert] from windows
with  sid='xxxxx'