Transferir logons e senhas entre instâncias do SQL Server

Artigo
10/17/2023

Este artigo descreve como transferir os logons e as senhas entre diferentes instâncias do SQL Server em execução no Windows.

Versão original do produto: SQL Server
Número original do KB: 918992, 246133

Introdução

Este artigo descreve como transferir os logons e senhas entre diferentes instâncias do Microsoft SQL Server.

Observação

As instâncias podem estar no mesmo servidor ou em servidores diferentes, e suas versões podem ser diferentes.

Mais informações

Neste artigo, os servidores A e B são servidores diferentes.

Depois de mover um banco de dados da instância de SQL Server no servidor A para a instância de SQL Server no servidor B, os usuários podem não conseguir fazer logon no banco de dados no servidor B. Além disso, os usuários poderão receber a seguinte mensagem de erro:

Falha no logon do usuário "MyUser" (Microsoft SQL Server, Erro: 18456)

Esse problema ocorre porque você não transferiu os logons e as senhas da instância de SQL Server no servidor A para a instância de SQL Server no servidor B.

Observação

A mensagem de erro 18456 também ocorre devido a outros motivos. Para obter informações adicionais sobre essas causas e possíveis resoluções, consulte MSSQLSERVER_18456.

Para transferir os logins, use um dos seguintes métodos, conforme apropriado à situação.

Método 1: redefinir a senha no computador de SQL Server de destino (Servidor B).

Para resolver esse problema, redefina a senha no computador do SQL Server e, em seguida, faça o script do logon.

Observação

O algoritmo de hash de senha é usado quando você redefine a senha.

Método 2: transferir logons e senhas para o servidor de destino (Servidor B) usando scripts gerados no servidor de origem (Servidor A).

Crie procedimentos armazenados que ajudarão a gerar scripts necessários para transferir logons e suas senhas. Para fazer isso, conecte-se ao Servidor A usando o SSMS (SQL Server Management Studio) ou qualquer outra ferramenta cliente e execute o seguinte 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'
          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, 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 + ']' + ', DEFAULT_LANGUAGE = [' + @defaultlanguage + ']'
                  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

Observação

Esse script cria dois procedimentos armazenados no banco de dados mestre. Os procedimentos são nomeados sp_hexadecimal e sp_help_revlogin.

No editor de consultas do SSMS, selecione a opção Resultados em Texto.
Execute a seguinte instrução na mesma janela ou em uma nova janela de consulta:
```
EXEC sp_help_revlogin
```
O script de saída gerado pelo procedimento sp_help_revlogin armazenado é o script de logon. Esse script de logon cria os logons que têm o SID (Identificador de Segurança) original e a senha original.

Importante

Examine as informações na seção Comentários a seguir antes de prosseguir com a implementação de etapas no servidor de destino.

Etapas no servidor de destino (Servidor B)

Conecte-se ao Servidor B usando qualquer ferramenta cliente (como o SSMS) e execute o script gerado na etapa 4 (saída de sp_helprevlogin) do Servidor A.

Comentários

Examine as seguintes informações antes de executar o script de saída na instância no servidor B:

Uma senha pode ter hash das seguintes maneiras:
- VERSION_SHA1: esse hash é gerado usando o algoritmo SHA1 e é usado no SQL Server 2000 ao SQL Server 2008 R2.
- VERSION_SHA2: esse hash é gerado usando o algoritmo SHA2 512 e é usado no SQL Server 2012 e versões posteriores.
Examine o script de saída com cuidado. Se o servidor A e o servidor B estão em domínios diferentes, você precisa alterar o script de saída. Em seguida, você precisa substituir o nome de domínio original usando o novo nome de domínio nas CREATE LOGIN instruções. Os logons integrados que recebem acesso no novo domínio não têm o mesmo SID que os logons no domínio original. Portanto, os usuários são órfãos desses logons. Para obter mais informações sobre como resolve esses usuários órfãos, consulte Solucionar problemas de usuários órfãos (SQL Server) e ALTER USER.
Se o servidor A e o servidor B estão no mesmo domínio, o mesmo SID é usado. Portanto, é improvável que os usuários sejam órfãos.
No script de saída, os logons são criados usando a senha criptografada. Isso ocorre devido ao argumento HASHED na instrução CREATE LOGIN. Esse argumento especifica que a senha inserida após o argumento PASSWORD já esteja com hash.
Por padrão, somente um membro da função de servidor fixo sysadmin pode executar uma instrução SELECT da exibição sys.server_principals. A menos que um membro da função de servidor fixa sysadmin conceda as permissões necessárias aos usuários, os usuários não podem criar ou executar o script de saída.
As etapas deste artigo não transferem as informações padrão do banco de dados para um logon específico. Isso ocorre porque o banco de dados padrão pode nem sempre existir no servidor B. Para definir o banco de dados padrão para um logon, use a ALTER LOGIN instrução passando o nome de logon e o banco de dados padrão como argumentos.
Classificar pedidos em servidores de origem e de destino:
- Servidor insensível a caso A e servidor com confidencialidade de caso B: a ordem de classificação do servidor A pode ser insensibilidade de caso e a ordem de classificação do servidor B pode ser sensível a casos. Nesse caso, os usuários devem digitar as senhas em todas as letras maiúsculas depois que você transferir os logons e as senhas para a instância no servidor B.
- Servidor sensível a casos A e servidor sem maiúsculas de maiúsculas de maiúsculas de minúsculas B: A ordem de classificação do servidor A pode ser sensível a casos e a ordem de classificação do servidor B pode ser insensibilidade de caso. Nesse caso, os usuários não podem fazer logon usando os logons e as senhas que você transfere para a instância no servidor B, a menos que uma das seguintes condições seja verdadeira:
  - As senhas originais não contêm letras.
  - Todas as letras nas senhas originais são letras maiúsculas.
- Sensível a maiúsculas de maiúsculas ou minúsculas em ambos os servidores: a ordem de classificação do servidor A e do servidor B pode ser sensível a casos ou a ordem de classificação do servidor A e do servidor B pode ser insensível a casos. Nesses casos, os usuários não enfrentam um problema.
Um logon que já está na instância no servidor B pode ter um nome que é o mesmo que um nome no script de saída. Nesse caso, você receberá a seguinte mensagem de erro ao executar o script de saída na instância no servidor B:

Mensagem 15025, Nível 16, Estado 1, Linha 1
A entidade de segurança do servidor 'MyLogin' já existe.

Da mesma forma, um logon que já está na instância no servidor B pode ter um SID que é o mesmo que um SID no script de saída. Nesse caso, você receberá a seguinte mensagem de erro ao executar o script de saída na instância no servidor B:

Msg 15433, Nível 16, Estado 1, Linha 1 Sid de parâmetro fornecido está em uso.

Portanto, você precisa fazer o seguinte:
1. Examine o script de saída com cuidado.
2. Examine o conteúdo da sys.server_principals exibição na instância no servidor B.
3. Solucione essas mensagens de erro conforme apropriado.
  
  No SQL Server 2005, o SID de um logon é usado para implementar o acesso no nível do banco de dados. Um logon pode ter SIDs diferentes em bancos de dados diferentes em um servidor. Nesse caso, o logon só pode acessar o banco de dados que tem o SID que corresponde ao SID na exibição sys.server_principals. Esse problema poderá ocorrer se os dois bancos de dados forem combinados de servidores diferentes. Para resolver esse problema, remova manualmente o logon do banco de dados que tem uma incompatibilidade de SID usando a instrução DROP USER. Em seguida, adicione o logon novamente usando a instrução CREATE USER.

Transferir logons e senhas entre instâncias do SQL Server

Introdução

Mais informações

Etapas no servidor de destino (Servidor B)

Comentários

Referências

Comentários

Comentários

Recursos adicionais