Cómo transferir inicios de sesión y contraseñas entre instancias de SQL Server 2005 y 2008

Si es usted un usuario de la edición para pequeñas empresas, podrá encontrar información adicional sobre la solución de problemas y recursos de aprendizaje en el sitio Support for Small Business (soporte para pequeñas empresas).

INTRODUCCIÓN

En este artículo se describe cómo transferir los inicios de sesión y las contraseñas entre instancias de Microsoft SQL Server 2005 y 2008 en distintos servidores.

Para obtener más información acerca de cómo transferir los inicios de sesión y las contraseñas entre instancias de versiones distintas de SQL Server, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:

246133 Cómo transferir inicios de sesión y contraseñas entre servidores SQL Server

Más información

En este artículo, el servidor A y el servidor B son servidores distintos. Además, ambos servidores ejecutan SQL Server 2005.

Nota: Esta información también es válida para SQL Server 2008.

Después de mover una base de datos de la instancia de SQL Server del servidor A a la instancia de SQL Server del servidor B, es posible que los usuarios no puedan iniciar sesión en la base de datos del servidor B. Además, los usuarios pueden recibir el mensaje de error siguiente:
Error de inicio de sesión del usuario 'MiUsuario'. (Microsoft SQL Server, error: 18456)
Este problema se produce porque no transfirió los inicios de sesión ni las contraseñas de la instancia de SQL Server del servidor A a la instancia de SQL del servidor B.

Para transferir los inicios de sesión y las contraseñas de la instancia de SQL Server del servidor A a la instancia de SQL Server del servidor B, siga estos pasos:
  1. En el servidor A, inicie SQL Server Management Studio y, a continuación, conéctese a la instancia de SQL Server desde la que movió la base de datos.
  2. Abra una nueva ventana del Editor de consultas y, a continuación, ejecute el siguiente script:
    USE master
    GO
    IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
    DROP PROCEDURE sp_hexadecimal
    GO
    CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
    AS
    DECLARE @charvalue varchar (514)
    DECLARE @i int
    DECLARE @length int
    DECLARE @hexstring char(16)
    SELECT @charvalue = '0x'
    SELECT @i = 1
    SELECT @length = DATALENGTH (@binvalue)
    SELECT @hexstring = '0123456789ABCDEF'
    WHILE (@i <= @length)
    BEGIN
    DECLARE @tempint int
    DECLARE @firstint int
    DECLARE @secondint int
    SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
    SELECT @firstint = FLOOR(@tempint/16)
    SELECT @secondint = @tempint - (@firstint*16)
    SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
    SELECT @i = @i + 1
    END

    SELECT @hexvalue = @charvalue
    GO

    IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
    DROP PROCEDURE sp_help_revlogin
    GO
    CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
    DECLARE @name sysname
    DECLARE @type varchar (1)
    DECLARE @hasaccess int
    DECLARE @denylogin int
    DECLARE @is_disabled int
    DECLARE @PWD_varbinary varbinary (256)
    DECLARE @PWD_string varchar (514)
    DECLARE @SID_varbinary varbinary (85)
    DECLARE @SID_string varchar (514)
    DECLARE @tmpstr varchar (1024)
    DECLARE @is_policy_checked varchar (3)
    DECLARE @is_expiration_checked varchar (3)

    DECLARE @defaultdb sysname

    IF (@login_name IS NULL)
    DECLARE login_curs CURSOR FOR

    SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
    sys.server_principals p LEFT JOIN sys.syslogins l
    ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
    ELSE
    DECLARE login_curs CURSOR FOR


    SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
    sys.server_principals p LEFT JOIN sys.syslogins l
    ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
    OPEN login_curs

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
    IF (@@fetch_status = -1)
    BEGIN
    PRINT 'No login(s) found.'
    CLOSE login_curs
    DEALLOCATE login_curs
    RETURN -1
    END
    SET @tmpstr = '/* sp_help_revlogin script '
    PRINT @tmpstr
    SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
    PRINT @tmpstr
    PRINT ''
    WHILE (@@fetch_status <> -1)
    BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@type IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group

    SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
    END
    ELSE BEGIN -- SQL Server authentication
    -- obtain password and sid
    SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
    EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
    EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

    -- obtain password policy state
    SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
    SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

    SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

    IF ( @is_policy_checked IS NOT NULL )
    BEGIN
    SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
    END
    IF ( @is_expiration_checked IS NOT NULL )
    BEGIN
    SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
    END
    END
    IF (@denylogin = 1)
    BEGIN -- login is denied access
    SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN -- login exists but does not have access
    SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN -- login is disabled
    SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
    END
    PRINT @tmpstr
    END

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
    END
    CLOSE login_curs
    DEALLOCATE login_curs
    RETURN 0
    GO
    Nota: Este script crea dos procedimientos almacenados en la base de datos maestra. Estos se denominan procedimiento almacenado sp_hexadecimal y sp_help_revlogin.
  3. Ejecute la instrucción siguiente:
    EXEC sp_help_revlogin
    El script de salida generado por el procedimiento almacenado sp_help_revlogin es el script de inicio de sesión. Este script de inicio de sesión crea los inicios de sesión que contienen el identificador de seguridad (SID) y la contraseña originales.
  4. En el servidor B, inicie SQL Server Management Studio y, a continuación, conéctese a la instancia de SQL Server a la que movió la base de datos.

    Importante: Antes de ir al paso 5, revise la información de la sección de comentarios.
  5. Abra una nueva ventana del Editor de consultas y, a continuación, ejecute el script de salida que se genera en el paso 3.

Comentarios

Revise la siguiente información antes de ejecutar el script de salida en la instancia del servidor B:
  • Revise atentamente el script de salida. Si los servidores A y B se encuentran en dominios distintos, debe modificar el script de salida. A continuación, deberá reemplazar el nombre del dominio original por el nombre del dominio nuevo en las instrucciones CREATE LOGIN. Los inicios de sesión integrados a los que se asigna acceso al dominio nuevo no tienen el mismo SID que los inicios de sesión del dominio original. Por lo tanto, los usuarios son huérfanos respecto a estos inicios de sesión. Para obtener más información acerca de cómo resolver los usuarios huérfanos, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:

    240872 Cómo resolver problemas de permisos cuando se traslada una base de datos entre servidores SQL Server

    Si el servidor A y el servidor B están en el mismo dominio, se usa el mismo SID. Por lo tanto, no es probable que los usuarios sean huérfanos.
  • En el script de salida, los inicios de sesión se crean mediante el uso de la contraseña cifrada. Esto se debe al argumento HASHED en la instrucción CREATE LOGIN. Este argumento especifica que la contraseña escrita tras el argumento PASSWORD ya tiene aplicado un algoritmo hash.
  • De forma predeterminada, sólo un miembro del rol fijo de servidor sysadmin puede ejecutar una instrucción SELECT desde la vista sys.server_principals. A menos que un miembro del rol fijo de servidor sysadmin conceda los permisos necesarios a los usuarios, estos no podrán crear ni ejecutar el script de salida.
  • Los pasos descritos en este artículo no transfieren la información de la base de datos predeterminada para un inicio de sesión específico. Esto se debe a que puede que la base de datos predeterminada no siempre exista en el servidor B. Para definir la base de datos predeterminada para un inicio de sesión, use la instrucción ALTER LOGIN con el nombre de inicio de sesión y la base de datos predeterminada como argumentos.
  • Puede que el criterio de ordenación del servidor A no distinga mayúsculas de minúsculas y el del servidor B sí. En este caso, los usuarios deben escribir todas las letras de las contraseñas en mayúsculas una vez se hayan transferido los inicios de sesión y las contraseñas a la instancia del servidor B.

    También puede que el criterio de ordenación del servidor A sí distinga mayúsculas de minúsculas y el del servidor B no. En este caso, los usuarios no podrán iniciar sesión utilizando los inicios de sesión y las contraseñas transferidos a la instancia del servidor B a menos que se cumpla una de las siguientes condiciones:
    • Las contraseñas originales no contienen letras.
    • Todas las letras de las contraseñas originales son mayúsculas.
    Puede que el criterio de ordenación de ambos servidores, A y B, distinga mayúsculas de minúsculas o que no lo haga en ninguno de los dos. En estos casos, los usuarios no experimentan ningún problema.
  • Puede que el nombre de un inicio de sesión que ya se encuentre en la instancia del servidor B coincida con otro del script de salida. En este caso, recibirá el mensaje de error siguiente al ejecutar el script de salida en la instancia del servidor B:
    Msj 15025, Nivel 16, Estado 1, Línea 1
    La entidad de servidor 'MiInicioDeSesión' ya existe.
    Del mismo modo, puede que un inicio de sesión que ya se encuentre en la instancia del servidor B tenga un SID que coincida con otro del script de salida. En este caso, recibirá el mensaje de error siguiente al ejecutar el script de salida en la instancia del servidor B:
    Msj 15433, Nivel 16, Estado 1, Línea 1
    El SID del parámetro especificado está en uso.
    Por lo tanto, debe hacer lo siguiente:
    1. Revise atentamente el script de salida.
    2. Examine el contenido de la vista sys.server_principals en la instancia del servidor B.
    3. Trate estos mensajes de error en consecuencia.
  • En SQL Server 2005, el SID de un inicio de sesión se usa como base para implementar el acceso de nivel de base de datos. Un inicio de sesión puede tener dos SID diferentes en dos bases de datos distintas de un servidor. En este caso, el inicio de sesión sólo puede tener acceso a la base de datos cuyo SID coincide con el de la vista sys.server_principals. Este problema puede producirse si se consolidan las dos bases de datos de dos servidores distintos. Para solucionarlo, quite manualmente el inicio de sesión de la base de datos cuyo SID no coincide mediante la instrucción DROP USER. A continuación, agregue de nuevo el inicio de sesión mediante la instrucción CREATE USER.

Referencias

Para obtener más información acerca de cómo solucionar los usuarios huérfanos, visite el siguiente sitio web de Microsoft Developer Network (MSDN): Para obtener más información acerca de la instrucción CREATE LOGIN, visite el siguiente sitio web de MSDN: Para obtener más información acerca de la instrucción ALTER LOGIN, visite el siguiente sitio web de MSDN:
Propiedades

Id. de artículo: 918992 - Última revisión: 17 abr. 2013 - Revisión: 1

Comentarios