Compartir a través de

Transferir inicios de sesión y contraseñas entre servidores SQL Server

  • Artículo

Este artículo describe cómo transferir inicios de sesión y las contraseñas entre distintas instancias de SQL Server que se ejecutan en Windows.

Versión del producto original: SQL Server
Número de KB original: 918992, 246133

Introducción

Este artículo describe cómo transferir inicios de sesión y contraseñas entre distintas instancias de Microsoft SQL Server. Las instancias pueden estar en el mismo servidor o en servidores diferentes, y sus versiones pueden diferir.

¿Por qué transferir inicios de sesión entre instancias de SQL Server?

En este artículo, el servidor A y el servidor B son servidores.

Después de mover una base de datos de una instancia de SQL Server en el servidor A a una instancia de SQL Server en el servidor B, es posible que los usuarios no puedan iniciar sesión en el servidor de base de datos en el servidor B. Además, es posible que los usuarios reciban el siguiente mensaje de error:

Error de inicio de sesión del usuario "MiUsuario". (Microsoft SQL Server, Error: 18456)

Este problema se produce porque los inicios de sesión de la instancia de SQL Server en el servidor A no existen en la instancia de SQL Server en el servidor B.

Tenga en cuenta que el error 18456 se produce por muchas otras razones. Para obtener más información sobre estas causas y sus resoluciones, consulte MSSQLSERVER_18456.

Pasos para transferir los inicios de sesión

Para transferir los inicios de sesión, utilice uno o varios de los métodos siguientes, según su situación.

Método 1: Generación de scripts a través de SSMS en el servidor de origen y restablecimiento manual de contraseñas para inicios de sesión de SQL Server en el servidor de destino

Puede generar scripts de inicio de sesión en SQL Server Management Studio (SSMS) mediante la opción Generar scripts para una base de datos.

Para generar scripts a través de SSMS en el servidor de origen y restablecer manualmente contraseñas para inicios de sesión de SQL Server en el servidor de destino, siga estos pasos:

  1. Conéctese al servidor A que hospeda el servidor SQL Server de origen.

  2. Expanda el nodo Bases de datos .

  3. Haga clic con el botón derecho en cualquier base de datos de usuario y seleccione Tareas>generar scripts.

  4. Se abre la página Introducción. Seleccione Siguiente para abrir la página Elegir objetos . Seleccione Crear un script a partir de toda la base de datos y todos los objetos de esta.

  5. Seleccione Siguiente para abrir la página Establecer opciones de scripting.

  6. Seleccione el botón Avanzadas para las opciones de inicio de sesión de script.

  7. En la lista Opciones avanzadas , busque Inicios de sesión de script, establezca la opción en True y seleccione Aceptar.

  8. Vuelva a Establecer opciones de scripting en Seleccione cómo se deben guardar los scripts y seleccione Abrir en la nueva ventana de consulta.

  9. Seleccione Siguiente dos veces y, a continuación, seleccione Finalizar.

  10. Busque la sección en el script que contiene inicios de sesión. Normalmente, el script generado contiene texto con el siguiente comentario al principio de esta sección:

    /* For security reasons the login is created disabled and with a random password. */

    Nota:

    Esto indica que los inicios de sesión de autenticación de SQL Server se generan con una contraseña aleatoria y están deshabilitados de forma predeterminada. Debe restablecer la contraseña y volver a habilitar estos inicios de sesión en el servidor de destino.

  11. Aplique el script de inicio de sesión desde el script generado más grande al servidor SQL Server de destino.

  12. Para los inicios de sesión de autenticación de SQL Server, restablezca la contraseña en el servidor SQL Server de destino y vuelva a habilitar esos inicios de sesión.

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

  1. Crear procedimientos almacenados que ayuden a generar los scripts necesarios para transferir inicios de sesión y sus contraseñas. Para ello, conéctese al servidor A mediante SQL Server Management Studio (SSMS) o cualquier otra herramienta cliente y ejecute el siguiente script:

    USE [master]
GO
IF OBJECT_ID('dbo.sp_hexadecimal') IS NOT NULL
    DROP PROCEDURE dbo.sp_hexadecimal
GO
CREATE PROCEDURE dbo.sp_hexadecimal
    @binvalue [varbinary](256)
    ,@hexvalue [nvarchar] (514) OUTPUT
AS
BEGIN
    DECLARE @i [smallint]
    DECLARE @length [smallint]
    DECLARE @hexstring [nchar](16)
    SELECT @hexvalue = N'0x'
    SELECT @i = 1
    SELECT @length = DATALENGTH(@binvalue)
    SELECT @hexstring = N'0123456789ABCDEF'
    WHILE (@i < =  @length)
    BEGIN
        DECLARE @tempint   [smallint]
        DECLARE @firstint  [smallint]
        DECLARE @secondint [smallint]
        SELECT @tempint = CONVERT([smallint], SUBSTRING(@binvalue, @i, 1))
        SELECT @firstint = FLOOR(@tempint / 16)
        SELECT @secondint = @tempint - (@firstint * 16)
        SELECT @hexvalue = @hexvalue
            + SUBSTRING(@hexstring, @firstint  + 1, 1)
            + SUBSTRING(@hexstring, @secondint + 1, 1)
        SELECT @i = @i + 1
    END
END
GO
IF OBJECT_ID('dbo.sp_help_revlogin') IS NOT NULL
    DROP PROCEDURE dbo.sp_help_revlogin
GO
CREATE PROCEDURE dbo.sp_help_revlogin
    @login_name [sysname] = NULL
AS
BEGIN
    DECLARE @name                  [sysname]
    DECLARE @type                  [nvarchar](1)
    DECLARE @hasaccess             [int]
    DECLARE @denylogin             [int]
    DECLARE @is_disabled           [int]
    DECLARE @PWD_varbinary         [varbinary](256)
    DECLARE @PWD_string            [nvarchar](514)
    DECLARE @SID_varbinary         [varbinary](85)
    DECLARE @SID_string            [nvarchar](514)
    DECLARE @tmpstr                [nvarchar](4000)
    DECLARE @is_policy_checked     [nvarchar](3)
    DECLARE @is_expiration_checked [nvarchar](3)
    DECLARE @Prefix                [nvarchar](4000)
    DECLARE @defaultdb             [sysname]
    DECLARE @defaultlanguage       [sysname]
    DECLARE @tmpstrRole            [nvarchar](4000)
    IF @login_name IS NULL
    BEGIN
        DECLARE login_curs CURSOR
        FOR
        SELECT p.[sid],p.[name],p.[type],p.is_disabled,p.default_database_name,l.hasaccess,l.denylogin,default_language_name = ISNULL(p.default_language_name,@@LANGUAGE)
        FROM sys.server_principals p
        LEFT JOIN sys.syslogins l ON l.[name] = p.[name]
        WHERE p.[type] IN ('S' /* SQL_LOGIN */,'G' /* WINDOWS_GROUP */,'U' /* WINDOWS_LOGIN */)
            AND p.[name] <> 'sa'
            AND p.[name] not like '##%'
        ORDER BY p.[name]
    END
    ELSE
        DECLARE login_curs CURSOR
        FOR
        SELECT p.[sid],p.[name],p.[type],p.is_disabled,p.default_database_name,l.hasaccess,l.denylogin,default_language_name = ISNULL(p.default_language_name,@@LANGUAGE)
        FROM sys.server_principals p
        LEFT JOIN sys.syslogins l ON l.[name] = p.[name]
        WHERE p.[type] IN ('S' /* SQL_LOGIN */,'G' /* WINDOWS_GROUP */,'U' /* WINDOWS_LOGIN */)
            AND p.[name] <> 'sa'
            AND p.[name] NOT LIKE '##%'
            AND p.[name] = @login_name
        ORDER BY p.[name]
    OPEN login_curs
    FETCH NEXT FROM login_curs INTO @SID_varbinary,@name,@type,@is_disabled,@defaultdb,@hasaccess,@denylogin,@defaultlanguage
    IF (@@fetch_status = - 1)
    BEGIN
        PRINT '/* No login(s) found for ' + QUOTENAME(@login_name) + N'. */'
        CLOSE login_curs
        DEALLOCATE login_curs
        RETURN - 1
    END
    SET @tmpstr = N'/* sp_help_revlogin script
** Generated ' + CONVERT([nvarchar], GETDATE()) + N' on ' + @@SERVERNAME + N'
*/'
    PRINT @tmpstr
    WHILE (@@fetch_status <> - 1)
    BEGIN
        IF (@@fetch_status <> - 2)
        BEGIN
            PRINT ''
            SET @tmpstr = N'/* Login ' + QUOTENAME(@name) + N' */'
            PRINT @tmpstr
            SET @tmpstr = N'IF NOT EXISTS (
    SELECT 1
    FROM sys.server_principals
    WHERE [name] = N''' + @name + N'''
    )
BEGIN'
            PRINT @tmpstr
            IF @type IN ('G','U') -- NT-authenticated Group/User
            BEGIN -- NT authenticated account/group 
                SET @tmpstr = N'    CREATE LOGIN ' + QUOTENAME(@name) + N'
    FROM WINDOWS
    WITH DEFAULT_DATABASE = ' + QUOTENAME(@defaultdb) + N'
        ,DEFAULT_LANGUAGE = ' + QUOTENAME(@defaultlanguage)
            END
            ELSE
            BEGIN -- SQL Server authentication
                -- obtain password and sid
                SET @PWD_varbinary = CAST(LOGINPROPERTY(@name, 'PasswordHash') AS [varbinary](256))
                EXEC dbo.sp_hexadecimal @PWD_varbinary, @PWD_string OUT
                EXEC dbo.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 = NCHAR(9) + N'CREATE LOGIN ' + QUOTENAME(@name) + N'
    WITH PASSWORD = ' + @PWD_string + N' HASHED
        ,SID = ' + @SID_string + N'
        ,DEFAULT_DATABASE = ' + QUOTENAME(@defaultdb) + N'
        ,DEFAULT_LANGUAGE = ' + QUOTENAME(@defaultlanguage)

                IF @is_policy_checked IS NOT NULL
                BEGIN
                    SET @tmpstr = @tmpstr + N'
        ,CHECK_POLICY = ' + @is_policy_checked
                END

                IF @is_expiration_checked IS NOT NULL
                BEGIN
                    SET @tmpstr = @tmpstr + N'
        ,CHECK_EXPIRATION = ' + @is_expiration_checked
                END
            END
            IF (@denylogin = 1)
            BEGIN -- login is denied access
                SET @tmpstr = @tmpstr
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'DENY CONNECT SQL TO ' + QUOTENAME(@name)
            END
            ELSE IF (@hasaccess = 0)
            BEGIN -- login exists but does not have access
                SET @tmpstr = @tmpstr
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'REVOKE CONNECT SQL TO ' + QUOTENAME(@name)
            END
            IF (@is_disabled = 1)
            BEGIN -- login is disabled
                SET @tmpstr = @tmpstr
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'ALTER LOGIN ' + QUOTENAME(@name) + N' DISABLE'
            END
            SET @Prefix =
                NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'EXEC [master].dbo.sp_addsrvrolemember @loginame = N'''
            SET @tmpstrRole = N''
            SELECT @tmpstrRole = @tmpstrRole
                + CASE WHEN sysadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''sysadmin''' ELSE '' END
                + CASE WHEN securityadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''securityadmin''' ELSE '' END
                + CASE WHEN serveradmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''serveradmin''' ELSE '' END
                + CASE WHEN setupadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''setupadmin''' ELSE '' END
                + CASE WHEN processadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''processadmin''' ELSE '' END
                + CASE WHEN diskadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''diskadmin''' ELSE '' END
                + CASE WHEN dbcreator = 1 THEN @Prefix + LoginName + N''', @rolename = N''dbcreator''' ELSE '' END
                + CASE WHEN bulkadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''bulkadmin''' ELSE '' END
            FROM (
                SELECT
                    SUSER_SNAME([sid])AS LoginName
                    ,sysadmin
                    ,securityadmin
                    ,serveradmin
                    ,setupadmin
                    ,processadmin
                    ,diskadmin
                    ,dbcreator
                    ,bulkadmin
                FROM sys.syslogins
                WHERE (    sysadmin <> 0
                        OR securityadmin <> 0
                        OR serveradmin <> 0
                        OR setupadmin <> 0
                        OR processadmin <> 0
                        OR diskadmin <> 0
                        OR dbcreator <> 0
                        OR bulkadmin <> 0
                        )
                    AND [name] = @name
                ) L
            IF @tmpstr <> '' PRINT @tmpstr
            IF @tmpstrRole <> '' PRINT @tmpstrRole
            PRINT 'END'
        END
        FETCH NEXT FROM login_curs INTO @SID_varbinary,@name,@type,@is_disabled,@defaultdb,@hasaccess,@denylogin,@defaultlanguage
    END
    CLOSE login_curs
    DEALLOCATE login_curs
    RETURN 0
END

    Nota:

    Este script crea dos procedimientos almacenados en la base de datos maestra. Los procedimientos se denominan sp_hexadecimal y sp_help_revlogin.

  2. En el editor de consultas de SSMS, seleccione la opción Resultados en texto.

  3. Ejecute la siguiente instrucción en la misma ventana de consulta o en otra diferente:

    EXEC sp_help_revlogin

  4. El script de salida que genera 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.

  5. Revise y siga la información de la sección Comentarios antes de continuar con los pasos de implementación en el servidor de destino.

  6. Una vez que implemente los pasos aplicables de la sección Comentarios , conéctese al servidor de destino B mediante cualquier herramienta de cliente (como SSMS).

  7. Ejecute el script generado como salida del sp_helprevlogin servidor A.

Comentarios

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

Información de hash de contraseñas

  • 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.

  • En el script de salida, los inicios de sesión se crean mediante el uso de la contraseña cifrada. Esto se debe al HASHED argumento de la CREATE LOGIN instrucción . Este argumento especifica que la contraseña que se escribe después de que el PASSWORD argumento ya esté hash.

Cómo controlar el cambio de dominios

¿Los servidores de origen y destino están en dominios diferentes? Revise atentamente el script de salida. Si los servidores A y B se encuentran en dominios distintos, debe cambiar el script de salida. A continuación, debe reemplazar el nombre de dominio original mediante el nuevo nombre de dominio en las CREATE LOGIN instrucciones . Los inicios de sesión integrados a los que se concede acceso en el nuevo dominio no tienen el mismo SID que los inicios de sesión en el 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 estos usuarios huérfanos, consulte Solución de problemas de usuarios huérfanos (SQL Server) y ALTER USER.

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.

Permisos para ver y seleccionar todos los inicios de sesión

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, los usuarios no pueden crear ni ejecutar el script de salida.

La configuración predeterminada de la base de datos no tiene scripts ni se transfiere

Los pasos de este artículo no transfieren la información de base de datos predeterminada para un inicio de sesión determinado. Esto se debe a que es posible 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 ALTER LOGIN instrucción pasando el nombre de inicio de sesión y la base de datos predeterminada como argumentos.

Cómo tratar diferentes pedidos de ordenación entre los servidores de origen y de destino

Puede haber diferencias en los pedidos de ordenación entre los servidores de origen y de destino, o pueden ser iguales. Este es el modo en que se puede abordar cada escenario:

  • Servidor que no distingue mayúsculas de minúsculas A y servidor B: el criterio de ordenación del servidor A podría no distinguir mayúsculas de minúsculas y el criterio de ordenación del servidor B podría distinguir mayúsculas de minúsculas. 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 que distingue mayúsculas de minúsculas A y servidor que no distingue mayúsculas de minúsculas B: el criterio de ordenación del servidor A podría distinguir mayúsculas de minúsculas y el criterio de ordenación del servidor B no distingue mayúsculas de minúsculas. En este caso, los usuarios no pueden iniciar sesión con los inicios de sesión y las contraseñas que se transfieren a la instancia en el servidor B a menos que se cumpla una de las condiciones siguientes:

    • Las contraseñas originales no contienen letras.
    • Todas las letras de las contraseñas originales son mayúsculas.

  • No distingue mayúsculas de minúsculas o no distingue mayúsculas de minúsculas en ambos servidores: el criterio de ordenación del servidor A y el servidor B podría distinguir entre mayúsculas y minúsculas, o el criterio de ordenación del servidor A y el servidor B podría no distinguir entre mayúsculas y minúsculas. En estos casos, los usuarios no experimentan un problema.

Cómo tratar los inicios de sesión ya existentes en el servidor de destino

El script está diseñado para comprobar si el inicio de sesión existe en el servidor de destino y crear un inicio de sesión solo si no lo hace. Sin embargo, si recibe el siguiente mensaje de error al ejecutar el script de salida en la instancia del servidor B, debe resolverlo manualmente siguiendo los pasos de esta sección.

Msj 15025, Nivel 16, Estado 1, Línea 1
La entidad de seguridad del servidor "MiInicioDeSesión" ya existe.

Del mismo modo, un inicio de sesión que ya está en la instancia del servidor B podría tener un SID que sea el mismo que un SID en el 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.

Para resolver manualmente el problema, siga estos pasos:

  1. Revise atentamente el script de salida.
  2. Examine el contenido de la sys.server_principals vista en la instancia del servidor B.
  3. Trate estos mensajes de error como convenga.

A partir de SQL Server 2005, el SID de un inicio de sesión se usa para administrar el acceso de nivel de base de datos. En ocasiones, un inicio de sesión podría tener diferentes SID cuando se asignan a los usuarios de distintas bases de datos. Este problema puede producirse si las bases de datos se combinan manualmente desde distintos servidores. En tales casos, el inicio de sesión solo puede tener acceso a la base de datos donde el SID de la entidad de seguridad de base de datos coincide con el SID en la sys.server_principals vista. Para resolver este problema, quite manualmente el usuario de la base de datos con el SID no coincidente mediante la instrucción DROP USER . A continuación, vuelva a agregar el usuario con la CREATE USER instrucción y asígnelo al inicio de sesión correcto (entidad de seguridad del servidor).

Para obtener más información y distinguir servidores de entidades de seguridad de base de datos, consulte CREATE USER y CREATE LOGIN.

Referencias