SQL Server - How to fix orphan users

 

Hi, now we will focus in how to fix orphan users, well, why happen this? here an example

    When we do database recovery on a new server, the link between ID and the user account not do math, because the original ID is ovewrite in the new box, for this reaso is necessary that they do match again, to do that, we will use the store procedure named  sp_change_users_login

    This store procedure have special parameters, these are:

    Report       -> Show all orphan users on database

    Auto_Fix   -> Fix ID broken

    Note: Remember you have got to be inside of database recovered, if not you will not see the orphan users 

--Example:
  
USE dbTest
GO

-- Show orphan users on dbTest 
EXEC  sp_change_users_login 'Report'    
                                            
-- Fix an user 
EXEC  sp_change_users_login 'Auto_Fix', 'user'    

-- Fix an specific user with login and password
EXEC  sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'  
                                                      

--Get all sentences to fix each orphan user found

USE dbTest

GO

DECLARE @temp TABLE(
UserName VARCHAR(70),
UserSID VARCHAR(150)
)

INSERT INTO @temp 
EXEC  sp_change_users_login 'Report'

SELECT  'EXEC  sp_change_users_login ''Auto_Fix'', ''' + UserName + ''''  
FROM @temp

--Result
-- EXEC sp_change_users_login 'Auto_Fix',  'UserTest1'
-- EXEC sp_change_users_login 'Auto_Fix',  'UserTest2'

 SQL Server - ¿Como reparar usuarios huerfanos?

    

Que tal, ahora nos enfocaremos a la recuperación de usuarios huerfanos en SQL Server, bueno ¿y por que pasa esto? mensionemos un caso práctico

    Cuando hacemos una recuperación de una bases de datos a un servidor nuevo, los ID que unen a los usuarios con los usuarios que se encuentran en la base, se rompen, es por ello que es necesario volverlos a sincronizar, para esto nos apoyaremos del procedimiento almacenado llamado sp_change_users_login

    Este procedimiento contiene parametros especiales para indicarle el método interno a ejecutar estos son:

• Report       -> Da una lista de los usuarios huerfanos
• Auto_Fix   -> Repara el ID roto del usuario

    Nota: recuerde que para realizar la reparación deberas estar en la base de datos que tiene los usuarios huerfanos, de otro modo no veras los que tienen problemas
    

--Ejemplos: 
        
USE dbTest
GO
-- Muestra los usuarios huerfanos de dbTest
EXEC  sp_change_users_login 'Report'   
                                           
-- Corrige a user   
EXEC  sp_change_users_login 'Auto_Fix', 'user'   
    
-- Corrige a user especificando login y password    
EXEC  sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'  
                                                     

--Obtiene las intrucciones para ejecutar y reparar los usuarios huerfanos

USE dbTest

GO

DECLARE @temp TABLE(
UserName VARCHAR(70),
UserSID VARCHAR(150)

)

INSERT INTO @temp 

EXEC  sp_change_users_login 'Report'

SELECT  'EXEC  sp_change_users_login ''Auto_Fix'', ''' + UserName + ''''  
FROM @temp

--Result

-- EXEC sp_change_users_login 'Auto_Fix',  'UserTest1'

-- EXEC sp_change_users_login 'Auto_Fix',  'UserTest2'