Compartir a través de


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

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.

Nota:

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

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 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, 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 no ha transferido 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 debido a otras razones. Para obtener información adicional sobre estas causas y posibles resoluciones, consulte MSSQLSERVER_18456.

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

Importante

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

Pasos en el servidor de destino (servidor B)

Conéctese al servidor B mediante cualquier herramienta de 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 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. 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.

  • 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, 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 ALTER LOGIN instrucción pasando 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 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.

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

    Por lo tanto, debe hacer lo siguiente:

    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.

      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 diferentes 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 cuyo SID coincide con el de la vista sys.server_principals. Este problema puede producirse si las dos bases de datos se combinan desde servidores diferentes. 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ónCREATE USER.

Referencias