Arreglar usuarios huérfanos en SQL Server 2008 (Orphaned Login) tras migración

Cuando migramos bases de datos importando/exportando entre diferentes servidores Microsoft SQL Server es muy probable que nos encontremos con el problema de la imposibilidad de autenticar contra el servidor con los usuarios asignados a la base de datos. Aunque los usuarios estén bien creados en la instancia de SQL Server y asignados a la base de datos correspondiente, veremos que el atributo/campo «Login name» aparece vacío y sin posibilidad de modificarlo.

Lo primero que debemos hacer es verificar que es este el problema, para ello lanzaremos una query contra la base de datos que nos reportará los SID (security identifiers) que hay en la base de datos pero que no están asignados a ningún Login name/SQL Server Login:

USE <base_de_datos>;
GO;
     sp_change_users_login @Action='Report';
GO;

Una vez detectados, podemos ir uno a uno arreglándolos:

USE <base_de_datos>;
GO
EXEC sp_change_users_login 'Auto_Fix', 'nombre_de_usuario';
GO

Y recibiremos el log indicando el fix del problema:

The row for user 'nombre_de_usuario' will be fixed by updating its login link to a login
already in existence.
The number of orphaned users fixed by updating users was 1.
The number of orphaned users fixed by adding new logins and then updating users was 0.

Existe un script/Transact-SQL, disponible en el sitio web de Microsoft y que podéis ver a continuación que simplifica este proceso, permitiendo arreglar todos los login de una base de datos directamente. Como podéis ver, incorpora un switch en la variable autoFix que permite mediante TRUE/FALSE especificar si queremos únicamente un reporte del estado de la base de datos o si automáticamente reparar todos los Login. Antes de Microsoft SQL Server 2008 existía un procedimiento almacenado / stored procedure que permitía solventar este problema, pero está obsoleta (sys.sp_change_users_login)..

/*
When you restore a database on a different SQL Server, all SQL users become a "orphaned user" because the SQL logins have a different SID then on the source server.
In SQL Server 2005 you can fix this orphaned user with the stored procedure "sys.sp_change_users_login", but since version 2008 this SP is deprecated.
This Transact-SQL statement also links orphaned user to existing principals with same name.
In regard of security this script don't add a new principal if none with same name was found; as the SP with option AUTO_FIX do.

Works with SQL Server 2005 and higher versions in all editions.
Requires ALTER ANY USER permissions.

Links:
  sys.sp_change_users_login: http://msdn.microsoft.com/en-us/library/ms174378.aspx
  ALTER USER: http://msdn.microsoft.com/en-us/library/ms176060.aspx
*/
-- Secure Orphaned User AutoFix 
DECLARE @autoFix bit; 
SET @autoFix = 'FALSE';  -- FALSE = Report only those user who could be auto fixed. 
                         -- TRUE  = Report and fix !!! 

DECLARE @user sysname, @principal sysname, @sql nvarchar(500), @found int, @fixed int; 

DECLARE orphans CURSOR LOCAL FOR 
    SELECT QUOTENAME(SU.[name]) AS UserName 
          ,QUOTENAME(SP.[name]) AS PrincipalName 
    FROM sys.sysusers AS SU 
         LEFT JOIN sys.server_principals AS SP 
             ON SU.[name] = SP.[name] 
                AND SP.[type] = 'S' 
    WHERE SU.issqluser = 1          -- Only SQL logins 
          AND NOT SU.[sid] IS NULL  -- Exclude system user 
          AND SU.[sid] <> 0x0       -- Exclude guest account 
          AND LEN(SU.[sid]) <= 16   -- Exclude Windows accounts & roles 
          AND SUSER_SNAME(SU.[sid]) IS NULL  -- Login for SID is null 
    ORDER BY SU.[name]; 

SET @found = 0; 
SET @fixed = 0; 
OPEN orphans; 
FETCH NEXT FROM orphans 
    INTO @user, @principal; 
WHILE @@FETCH_STATUS = 0 
BEGIN 
    IF @principal IS NULL 
        PRINT N'Orphan: ' + @user; 
    ELSE 
    BEGIN 
        PRINT N'Orphan: ' + @user + N' => Autofix possible, principal with same name found!'; 
        IF @autoFix = 'TRUE' 
        BEGIN 
            -- Build the DDL statement dynamically. 
            SET @sql = N'ALTER USER ' + @user + N' WITH LOGIN = ' + @principal + N';'; 
            EXEC sp_executesql @sql; 
            PRINT N'        ' + @user + N' is auto fixed.'; 
            SET @fixed = @fixed + 1; 
        END 
    END 
    SET @found = @found + 1; 

    FETCH NEXT FROM orphans 
        INTO @user, @principal; 
END; 

CLOSE orphans; 
DEALLOCATE orphans; 

PRINT ''; 
PRINT CONVERT(nvarchar(15), @found) + N' orphan(s) found, ' 
    + CONVERT(nvarchar(15), @fixed) + N' orphan(s) fixed.';