Frecuentemente necesitamos restaurar una base de datos procedente de otro servidor distinto al nuestro, o mover una base de datos de un servidor a otro; por ejemplo, la base de datos de producción de nuestro sistema en nuestro servidor de pruebas. Estos procesos son sencillos, pero si no se tienen claros algunos conceptos nos pueden ocasionar más complicaciones de las esperadas.

Para entender el proceso debemos tener claras dos definiciones. SQL Server tiene identificadores de inicio de sesión y usuarios de base de datos:

El identificador de inicio de sesión está asociado a la instancia del SQL Server.
El usuario de base de datos está asociado una base de datos. Cuando se agrega un usuario a la base de datos, éste usuario tiene que ser un identificador de inicio de sesión de la instancia de SQL Server.

Cuando restauremos (o adjuntemos) nuestra base de datos, estaremos restaurando (o llevando) un usuario de base de datos que debe estar asociado a un inicio de sesión. Aquí es donde comienza nuestro problema: es muy posible que la instalación en ambos servidores no haya sido la misma, y también es bastante posible que los identificadores de inicio de sesión (su ID interno) no coincida, por lo que el usuario de base de datos no estará bien asociado al identificador de inicio de sesión. Para comprobar esto, despues de haber restaurado la base de datos, abrimos el EM, abrimos nuestra base de datos, y en la sección de usuarios buscamos nuestro usuario de base de datos (en mi caso prueba), y se comprueba que el usuario no está asociado a un inicio de sesión.

Nota: Internamente, los inicios de sesión se encuentran en master..sysxlogins y los usuarios de la base de datos en <bd>.<dbo>.sysusers por lo que es fácil que los sid en ambas tablas son diferentes (de ahí vienen las referencias al identifiador interno).

select uid, name, sid from sysusers
where name = 'prueba'

select srvid, sid, name from master..sysxlogins
where name = 'prueba'

uid name sid
------ ------------------ -------------------------------------
5 prueba 0x571EBC5DC1C54545A18B4F8DC6CD0DCE

(1 row(s) affected)

srvid sid name
------ ------------------------------------------- ------------
NULL 0x85022BFD51D2DE46A2143548658D4430 prueba

Una de las opciones podría ser, ir a los "Inicios de Sesión" de nuestro servidor en la opción "Seguridad", y al usuario prueba, asignarle la base de datos por defecto, pero nos indicará que al inicio de sesión prueba no se le ha concedido permiso para acceder a su base de datos por defecto.

Para resolver este problema, SQL Server tiene un procedimiento almacenado especial para re-vincular el inicio de sesión con el usuario de la base de datos (aunque el nombre sea el mismo como hemos comprobado, su identificador interno también tiene que ser igual):

sp_change_users_login

Para comprobar los usuarios de base de datos que no tienen correspondencia con ningún inicio de sesión ejecutaremos:

sp_change_users_login 'Report'

Para vincular el usuario de base de datos con su inicio de sesión correspondiente, ejecutaremos:

sp_change_users_login 'prueba'

donde 'prueba' es el usuario al que queremos asignarle su inicio de sesión.

Si nuestra base de datos tiene muchos usuarios por vincular, podría utilizarse el siguiente batch que perfectamente se podría crear como procedimiento almacenado:

DECLARE cUsuarios CURSOR READ_ONLY
FOR
select UserName = name from sysusers
where issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
order by name

DECLARE @Usuario varchar(4000)
DECLARE @cadena varchar(4000)
OPEN cUsuarios

FETCH NEXT FROM cUsuarios INTO @Usuario
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SET @cadena = 'sp_change_users_login ''Auto_Fix'',''' + @Usuario + ''''
EXEC ( @CADENA )
END
FETCH NEXT FROM cUsuarios INTO @Usuario
END

CLOSE cUsuarios
DEALLOCATE cUsuarios

Lo único que hace es recorrer todos lo usuarios que no tienen asociado inicio de sesión consultando a las tablas del sistema y mendiante una sentencia dinámica "EXEC ( @Cadena )" llamar al procedimiento almacenado sp_change_users_login.

Existen otras formas de re-vincular los inicios de sesión modificando directamente las tablas de sistema, pero es un uso no recomendable modificar las tablas del sistema.

Otros enlaces interesantes:

http://www.programacion.com/bbdd/articulo/man_moverbasesdatos/

Para cualquier tipo de sugerencia, colaboración o comunicación, diríjanse a

webmaster@helpdna.net