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

En este artículo se describe cómo transferir los inicios de sesión y las contraseñas entre diferentes instancias de SQL Server se ejecutan en Windows.

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

Introducción

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

Nota

Las instancias pueden estar en el mismo servidor o en servidores diferentes, y sus versiones pueden ser diferentes.

Más información

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

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

Error de inicio de sesión para el usuario 'MyUser'. (Microsoft SQL Server, Error: 18456)

Este problema se produce porque no transfería los inicios de sesión y las contraseñas de la instancia de SQL Server en el servidor A a la instancia de SQL Server en el servidor B.

Nota

El mensaje de error 18456 también se produce por otros motivos. Para obtener información adicional sobre estas causas y posibles resoluciones, revise MSSQLSERVER_18456.

Para transferir los inicios de sesión, use uno de los siguientes métodos, según corresponda para su situación.

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

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

    Nota

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

  • 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. Cree procedimientos almacenados que le ayudarán a generar 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 ('sp_hexadecimal') IS NOT NULL
        DROP PROCEDURE sp_hexadecimal
        GO
        CREATE PROCEDURE [dbo].[sp_hexadecimal]
        (
            @binvalue varbinary(256),
            @hexvalue varchar (514) OUTPUT
        )
        AS
        BEGIN
            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
        END
        go
        IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
        DROP PROCEDURE sp_help_revlogin
        GO
        CREATE PROCEDURE [dbo].[sp_help_revlogin]   
        (
            @login_name sysname = NULL 
        )
        AS
        BEGIN
            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 @Prefix                   VARCHAR(255)
            DECLARE @defaultdb                SYSNAME
            DECLARE @defaultlanguage          SYSNAME     
            DECLARE @tmpstrRole               VARCHAR (1024)
      
        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, p.default_language_name  
                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'
                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, p.default_language_name  
                    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
                    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.'
                      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
      
                        SET @tmpstr='IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'''+@name+''')
                        BEGIN'
                        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 + ']' + ', DEFAULT_LANGUAGE = [' + @defaultlanguage + ']'
      
                                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 
      
                SET @Prefix = '
                EXEC master.dbo.sp_addsrvrolemember @loginame='''
      
                SET @tmpstrRole=''
      
                SELECT @tmpstrRole = @tmpstrRole
                    + CASE WHEN sysadmin        = 1 THEN @Prefix + [LoginName] + ''', @rolename=''sysadmin'''        ELSE '' END
                    + CASE WHEN securityadmin   = 1 THEN @Prefix + [LoginName] + ''', @rolename=''securityadmin'''   ELSE '' END
                    + CASE WHEN serveradmin     = 1 THEN @Prefix + [LoginName] + ''', @rolename=''serveradmin'''     ELSE '' END
                    + CASE WHEN setupadmin      = 1 THEN @Prefix + [LoginName] + ''', @rolename=''setupadmin'''      ELSE '' END
                    + CASE WHEN processadmin    = 1 THEN @Prefix + [LoginName] + ''', @rolename=''processadmin'''    ELSE '' END
                    + CASE WHEN diskadmin       = 1 THEN @Prefix + [LoginName] + ''', @rolename=''diskadmin'''       ELSE '' END
                    + CASE WHEN dbcreator       = 1 THEN @Prefix + [LoginName] + ''', @rolename=''dbcreator'''       ELSE '' END
                    + CASE WHEN bulkadmin       = 1 THEN @Prefix + [LoginName] + ''', @rolename=''bulkadmin'''       ELSE '' END
                  FROM (
                            SELECT CONVERT(VARCHAR(100),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 
      
                    PRINT @tmpstr
                    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 SSMS consulta, seleccione la opción Resultados a texto.

    3. Ejecute la siguiente instrucción en la misma ventana de consulta o en una nueva:

      EXEC sp_help_revlogin
      
    4. El script de salida que genera sp_help_revlogin el procedimiento almacenado es el script de inicio de sesión. Este script de inicio de sesión crea los inicios de sesión que tienen el identificador de seguridad (SID) original y la contraseña original.

Importante

Revise la información de la sección Comentarios a continuación antes de continuar con la implementación de pasos en el servidor de destino.

Pasos en el servidor de destino (servidor B)

Conectar al servidor B con cualquier herramienta cliente (como SSMS) y, a continuación, ejecute el script generado en el paso 4 (salida de sp_helprevlogin ) desde el servidor A.

Comentarios

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

  • Una contraseña se puede hash de las siguientes maneras:

    • VERSION_SHA1: Este hash se genera mediante el algoritmo SHA1 y se usa en SQL Server 2000 a SQL Server 2008 R2.
    • VERSION_SHA2: este hash se genera mediante el algoritmo SHA2 512 y se usa en SQL Server 2012 y versiones posteriores.
  • Revise cuidadosamente el script de salida. Si el servidor A y el servidor B están en dominios diferentes, debe cambiar el script de salida. A continuación, debe reemplazar el nombre de dominio original mediante el nuevo nombre de dominio en las instrucciones CREATE LOGIN. 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 quedan huérfanos de estos inicios de sesión. Para obtener más información acerca de cómo resolver estos usuarios huérfanos, vea How to resolve permission issues when you move a database between servers that are running SQL Server.

    Si el servidor A y el servidor B están en el mismo dominio, se usa el mismo SID. Por lo tanto, es poco probable que los usuarios estén huérfanos.

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

  • De forma predeterminada, solo un miembro del rol fijo de servidor sysadmin puede ejecutar una SELECT instrucción desde la sys.server_principals vista. 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.

  • 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 instrucción pasando el nombre de inicio de sesión y la base de datos ALTER LOGIN predeterminada como argumentos.

  • Ordenar pedidos en servidores de origen y de destino:

    • Servidor que distingue mayúsculas de minúsculas A y servidor que distingue mayúsculas de minúsculas B: el criterio de ordenación del servidor A puede no distingue mayúsculas de minúsculas y el criterio de ordenación del servidor B puede distingue mayúsculas de minúsculas. En este caso, los usuarios deben escribir las contraseñas en todas las letras mayúsculas después de transferir los inicios de sesión y las contraseñas a la instancia en el servidor B.

    • Servidor A que distingue mayúsculas de minúsculas y servidor que no distingue mayúsculas de minúsculas B: El criterio de ordenación del servidor A puede distingue mayúsculas de minúsculas y el criterio de ordenación del servidor B puede 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 cumple una de las siguientes condiciones:

      • Las contraseñas originales no contienen letras.
      • Todas las letras de las contraseñas originales son mayúsculas.
    • Distingue mayúsculas de minúsculas o distingue mayúsculas de minúsculas en ambos servidores: el criterio de ordenación del servidor A y del servidor B puede distingue mayúsculas de minúsculas, o el criterio de ordenación del servidor A y del servidor B puede no distingue mayúsculas de minúsculas. En estos casos, los usuarios no experimentan ningún problema.

  • Un inicio de sesión que ya está en la instancia en el servidor B puede tener un nombre que sea el mismo que un nombre en el script de salida. En este caso, recibirá el siguiente mensaje de error al ejecutar el script de salida en la instancia en el servidor B:

    Msg 15025, Level 16, State 1, Line 1
    La entidad de seguridad del servidor 'MyLogin' ya existe.

    Del mismo modo, un inicio de sesión que ya está en la instancia en el servidor B puede tener un SID que sea el mismo que un SID en el script de salida. En este caso, recibirá el siguiente mensaje de error al ejecutar el script de salida en la instancia en el servidor B:

    Msg 15433, Level 16, State 1, Line 1 Supplied parameter sid is in use.

    Por lo tanto, debe hacer lo siguiente:

    1. Revise cuidadosamente el script de salida.

    2. Examine el contenido de la vista sys.server_principals en la instancia del servidor B.

    3. Direccione estos mensajes de error según corresponda.

      En SQL Server 2005, el SID de un inicio de sesión se usa para implementar el acceso a nivel de base de datos. Un inicio de sesión puede tener distintos SID en bases de datos diferentes en un servidor. En este caso, el inicio de sesión solo puede tener acceso a la base de datos que tiene el SID que coincide con el SID de la sys.server_principals vista. Este problema puede producirse si las dos bases de datos se combinan desde distintos servidores. Para resolver este problema, quite manualmente el inicio de sesión de la base de datos que tiene un error de coincidencia de SID mediante la instrucción DROP USER. A continuación, agregue de nuevo el inicio de sesión mediante la CREATE USER instrucción.

Referencias