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

Se aplica a: Microsoft SQL Server 2005 Standard EditionMicrosoft SQL Server 2005 Workgroup EditionMicrosoft SQL Server 2005 Developer Edition

INTRODUCCIÓN


Este artículo describe cómo transferir inicios de sesión y contraseñas entre distintas instancias de Microsoft SQL Server.

Nota Las instancias pueden estar en el mismo servidor o en servidores distintos, y sus versiones también pueden diferir.

Para obtener más información sobre cómo transferir inicios de sesión y 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 instancias de SQL Server que ejecutan versiones anteriores de SQL Server

Más información


En este artículo, el servidor A y el servidor B son distintos. 
 
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, 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, utilice uno o varios de los métodos siguientes, según su situación.

Método 1: Restablecer la contraseña en el equipo con SQL Server de destino (servidor B)

Para resolver este problema, restablezca la contraseña en el equipo con SQL Server y luego elimine del script el inicio de sesión.

Nota El algoritmo hash de contraseña se usa al restablecer la contraseña.

Método 2: Transferir los inicios de sesión y las contraseñas al servidor de destino (servidor A) usando los scripts generados en el servidor de origen (servidor B)

Para crear un registro en un script que tenga una contraseña en blanco, siga estos pasos:
  1. En el servidor A, inicie SQL Server Management Studio y luego 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. Los procedimientos se llaman sp_hexadecimal y sp_help_revlogin.
  3. Ejecute la siguiente instrucción en la misma ventana de consulta o en otra diferente: 
    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.

 Pasos en el servidor de destino (servidor B):

  1. En el servidor B, inicie SQL Server Management Studio y después conéctese a la instancia de SQL Server a la que movió la base de datos.

    Importante Antes de ir al paso 2, revise la información de la sección de "Comentarios" (abajo).
  2. Abra una nueva ventana del Editor de consultas y luego ejecute el script de salida que se genera en el paso 2 del procedimiento anterior.

Comentarios

Revise la siguiente información antes de ejecutar el script de salida en la instancia del servidor B:

  • A una contraseña se le puede aplicar un algoritmo hash de las maneras siguientes.
    • VERSION_SHA1: este hash se genera usando el algoritmo SHA1 y se emplea desde SQL Server 2000 hasta SQL Server 2008 R2.
    • VERSION_SHA2: este hash se genera usando el algoritmo SHA2 512 y se emplea en SQL Server 2012 y versiones posteriores.
  • Revise atentamente el script de salida. Si los servidores A y B se encuentran en dominios distintos, debe cambiar el script de salida. Después deberá reemplazar el nombre del dominio original usando el nombre del dominio nuevo en las instrucciones CREATE LOGIN. Los inicios de sesión integrados a los que se asigna acceso en el 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 sobre 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 que ejecutan 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, solo 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.
  • Criterios de ordenación en los servidores de origen y destino:
    • Servidor A que no distingue mayúsculas de minúsculas y servidor B que sí: es posible 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 que se hayan transferido los inicios de sesión y las contraseñas a la instancia del servidor B.
    • Servidor A que distingue mayúsculas de minúsculas y servidor B que no: es posible que el criterio de ordenación del servidor A 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.
    • Los dos servidores distinguen mayúsculas de minúsculas o ninguno de los dos lo hace: es posible que el criterio de ordenación de ambos servidores, A y B, distinga mayúsculas de minúsculas o que no lo haga el de 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 seguridad del 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 como convenga.

      En SQL Server 2005, el SID de un inicio de sesión se usa para implementar el acceso de nivel de base de datos. Un inicio de sesión puede tener SID diferentes en bases de datos distintas de un servidor. En este caso, el inicio de sesión solo 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 combinan las dos bases de datos de 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.
  • Si intenta crear un nuevo inicio de sesión de SQL Server 2012 usando otro anterior a SQL Server 2000 generado por script, recibirá el siguiente mensaje de error:

    Msj 15021, Nivel 16, Estado 2, Línea 1
    Valor no válido dado para el parámetro PASSWORD. Especifique un valor de parámetro válido.

    Nota Este error se recibe en SQL Server 2012 por el hash de contraseña de 16 bytes que se suministra para las instrucciones CREATE LOGIN y ALTER LOGIN.

    Para resolver este problema en un servidor que ejecute SQL Server 2012, cree un inicio de sesión que contenga una contraseña en blanco. Para ello, ejecute la siguiente secuencia de comandos:
    CREATE LOGIN [Test] WITH PASSWORD = '', SID = 0x90FD605DCEFAE14FAB4D5EB0BBA1AECC, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF
    Después de crear el inicio de sesión con la contraseña en blanco, el usuario puede cambiarla en el siguiente intento de inicio de sesión.

Método 3: Iniciar sesión usando la contraseña anterior a SQL Server 2000

Nota Este método solo sirve si va a migrar SQL Server 2000 a una versión compatible de SQL Server que sea más reciente.

En esta situación, pida al usuario que inicie sesión en el servidor que ejecuta SQL Server mediante el inicio de sesión anterior a SQL Server 2000.

Nota El hash de contraseña se actualiza automáticamente cuando el usuario inicia sesión con la contraseña anterior a SQL Server 2000.

Referencias


Para obtener más información sobre cómo solucionar los problemas de los usuarios huérfanos, visite el sitio web Solucionar problemas de usuarios huérfanos de Microsoft Developer Network (MSDN).

Para obtener más información sobre la instrucción CREATE LOGIN, visite el sitio web CREATE LOGIN (Transact-SQL) de MSDN.

Para obtener más información sobre la instrucción ALTER LOGIN, visite el sitio web ALTER LOGIN (Transact-SQL) de MSDN.