Transferir logons e senhas entre instâncias do SQL Server

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

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

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, o servidor A e o servidor B são servidores diferentes.

Depois de mover um banco de dados da instância do SQL Server no servidor A para a instância do 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 podem receber a seguinte mensagem de erro:

Falha de logon para o usuário 'MyUser'. (Microsoft SQL Server, erro: 18456)

Esse problema ocorre porque você não transferiu os logons e as senhas da instância do SQL Server no servidor a para a instância do 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 revisões de resoluções MSSQLSERVER_18456.

Para transferir os logons, use um dos seguintes métodos, conforme apropriado para a sua situação.

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

    Para resolver esse problema, redefina a senha no computador do SQL Server e 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)

    1. Criar procedimentos armazenados que irão ajudar a gerar scripts necessários para transferir logons e suas senhas. Para fazer isso, conecte-se ao servidor A usando o SQL Server Management Studio (SSMS) ou qualquer outra ferramenta de cliente e execute o seguinte 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
      

      Observação

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

    2. No editor de consulta SSMS, selecione a opção resultados para texto .

    3. Execute a seguinte instrução no mesmo ou em uma nova janela de consulta:

      EXEC sp_help_revlogin
      
    4. O script de saída sp_help_revlogin gerado pelo procedimento armazenado é o script de logon. Esse script de logon cria os logons que têm o identificador de segurança (SID) original e a senha original.

Importante

Revise as informações na seção comentários abaixo 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 de cliente (como SSMS) e execute o script gerado na etapa 4 (saída de sp_helprevlogin ) do servidor A.

Comentários

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

  • Uma senha pode ser codificada das seguintes maneiras:

    • VERSION_SHA1: Esse hash é gerado usando o algoritmo SHA1 e é usado no SQL Server 2000 por meio do SQL Server 2008 R2.
    • VERSION_SHA2: Esse hash é gerado usando o algoritmo SHA2 512 e é usado no SQL Server 2012 e em versões posteriores.
  • Revise o script de saída cuidadosamente. Se o servidor A e o servidor B estiverem em domínios diferentes, você precisará 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 instruções CREATE LOGIN. 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 ficam órfãos desses logons. Para obter mais informações sobre como resolver esses usuários órfãos, consulte como resolver problemas de permissão ao mover um banco de dados entre servidores que estão executando o SQL Server.

    Se o servidor A e o servidor B estiverem no mesmo domínio, o mesmo SID será usado. Portanto, é improvável que os usuários fiquem órfãos.

  • No script de saída, os logons são criados usando a senha criptografada. Isso se deve ao argumento HASH na CREATE LOGIN instrução. Este argumento especifica que a senha inserida após o argumento PASSWORD já é hash.

  • Por padrão, apenas um membro da função de servidor fixa sysadmin pode executar uma SELECT instrução do sys.server_principals modo de exibição. 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 neste artigo não transferem as informações de banco de dados padrão para um determinado login. Isso ocorre porque o banco de dados padrão pode não existir sempre 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.

  • Ordenar pedidos nos servidores de origem e de destino:

    • Servidor que não diferencia maiúsculas de minúsculas e servidor B: a ordem de classificação do servidor a pode diferenciar maiúsculas de minúsculas, e a ordem de classificação do servidor B pode diferenciar maiúsculas de minúsculas. 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 que diferencia maiúsculas de minúsculas e servidor B: A ordem de classificação do servidor A pode diferenciar maiúsculas de minúsculas, e a ordem de classificação do servidor B pode diferenciar maiúsculas de minúsculas. 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 estão em letras maiúsculas.
    • Diferenciar maiúsculas de minúsculas ou diferenciar maiúsculas de minúsculas em ambos os servidores: a ordem de classificação do servidor a e do servidor b pode diferenciar maiúsculas de minúsculas, ou a ordem de classificação do servidor a e do servidor b pode diferenciar maiúsculas de minúsculas. 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 igual ao nome do script de saída. Nesse caso, você receberá a seguinte mensagem de erro quando executar o script de saída na instância no servidor B:

    MSG 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 quando executar o script de saída na instância no servidor B:

    MSG 15433, nível 16, estado 1, linha 1 o parâmetro fornecido por Sid está em uso.

    Portanto, você deve fazer o seguinte:

    1. Revise o script de saída cuidadosamente.

    2. Examine o conteúdo do modo de exibição sys.server_principals na instância no servidor B.

    3. Solucionar 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 diferentes SIDs em diferentes bancos de dados em um servidor. Nesse caso, o logon só pode acessar o banco de dados que tem o SID que corresponde ao SID no sys.server_principals modo de exibição. Esse problema pode 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 CREATE USER instrução.

Referências