Como transferir inícios de sessão e palavras-passe entre instâncias do SQL Server

Aplica-se a: Microsoft SQL Server 2005 Standard EditionMicrosoft SQL Server 2005 Workgroup EditionMicrosoft SQL Server 2005 Developer Edition

INTRODUÇÃO


Este artigo descreve como transferir os inícios de sessão e palavras-passe entre diferentes instâncias do Microsoft SQL Server.

Nota As instâncias podem estar no mesmo servidor ou em diferentes servidores e as respetivas versões poderão ser diferentes.

Para obter mais informações sobre como transferir inícios de sessão e palavras-passe entre instâncias do SQL Server, clique no número de artigo que se segue para visualizar o artigo na Base de Dados de Conhecimento da Microsoft:

246133 Como transferir inícios de sessão e palavras-passe entre instâncias de SQL Server que estejam a executar versões mais antigas do SQL Server

Mais Informações


Neste artigo, o servidor A e o servidor B são servidores diferentes. 
 
Depois de mover uma base de dados da instância do SQL Server no servidor A para a instância do SQL Server no servidor B, os utilizadores poderão não conseguir iniciar sessão na base de dados no servidor B. Além disso, os utilizadores poderão receber a seguinte mensagem de erro:
O início de sessão falhou para o utilizador 'MyUser'. (Microsoft SQL Server, Erro: 18456)
Este problema ocorre porque não transfere os inícios de sessão e as palavras-passe da instância do SQL Server no servidor A para a instância do SQL Server no servidor B.

Para resolver este problema, utilize um dos seguintes métodos, conforme adequado à situação.

Método 1: Repor a palavra-passe no computador SQL Server de destino (Servidor B)

Para resolver este problema, reponha a palavra-passe no computador SQL Server e, em seguida, faça um script de inicio de sessão.

Nota O algoritmo hash de password é utilizado quando repõe a password.

Método 2: Transferir inícios de sessão e palavras-passe para o servidor de destino (Servidor A) utilizando scripts gerados no servidor de origem (Servidor B)

Para criar um script de início de sessão que tenha uma palavra-passe em branco, siga estes passos:
  1. No servidor A, inicie o SQL Server Management Studio e depois ligue à instância do SQL Server a partir do qual moveu a base de dados.
  2. Abra uma nova janela Editor de Consulta 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


    Nota : este script cria dois procedimentos armazenados na base de dados principal. Os procedimentos são denominados sp_hexadecimal e sp_help_revlogin.
  3. Execute a seguinte instrução na mesma janela de consulta ou numa nova janela: 
    EXEC sp_help_revlogin
    O script de saída que o procedimento armazenado sp_help_revlogin gera é o script de início de sessão. Este script de início de sessão cria as autenticações que têm o Identificador de Segurança (SID) original e a palavra-passe original.

 Passos no servidor de destino (Servidor B):

  1. No servidor B, inicie o SQL Server Management Studio e depois ligue à instância do SQL Server para onde moveu a base de dados.

    Importante Antes de avançar para o passo 2, reveja a informação na secção "Observações" abaixo.
  2. Abra uma nova janela Editor de Consulta e execute o script de saída que é gerado no passo 2 do procedimento anterior.

Observações

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

  • Uma palavra-passe pode ser colocada em hash das seguintes formas.
    • VERSION_SHA1: Este hash é gerado utilizando o algoritmo SHA1 e é utilizado do SQL Server 2000 até ao SQL Server 2008 R2.
    • VERSION_SHA2: Este hash é gerado utilizando o algoritmo SHA2 512 e é utilizado no SQL Server 2012 e versões posteriores.
  • Reveja cuidadosamente o script de saída. Se o servidor A e o servidor B estiverem em domínios diferentes, terá de alterar o script de saída. Em seguida, tem de substituir o nome do domínio original utilizando o novo nome de domínio nas instruções CREATE LOGIN. Os inícios de sessão integrados a que são concedidos acesso no novo domínio não têm o mesmo SID que os inícios de sessão no domínio original. Assim, os utilizadores são órfãos destes inícios de sessão. Para mais informações sobre como resolver estes utilizadores órfãos, clique no número de artigo que se segue para visualizar o artigo na Base de Dados de Conhecimento Microsoft:

    240872 Como solucionar questões de permissão quando mover uma base de dados entre servidores que se encontrem a executar o SQL Server

    Se o servidor A e o servidor B estiverem no mesmo domínio, é utilizado o mesmo SID. Assim, não é provável que os utilizadores fiquem órfãos.
  • No script de saída, os inícios de sessão são criados utilizando a palavra-passe encriptada. Isto deve-se ao argumento HASHED na instrução CREATE LOGIN. Este argumento especifica que a palavra-passe introduzida após o argumento PALAVRA-PASSE já se encontra em hash.
  • Por predefinição, apenas um membro da função de servidor fixo sysadmin pode executar uma instrução SELECT a partir da vista sys.server_principals . A menos que um membro da função de servidor fixo sysadmin conceda as permissões necessárias aos utilizadores, os utilizadores não poderão criar ou executar o script de saída.
  • Os passos neste artigo não transferem as informações de base de dados predefinidas para um início de sessão específico. Isto deve-se ao facto de a base de dados predefinida poder não existir sempre no servidor B. Para definir a base de dados predefinida para um início de sessão, utilize a instrução ALTER LOGIN passando o nome de início de sessão e a base de dados predefinida como argumentos.
  • Ordenar sequências nos servidores de origem e de destino:
    • Servidor A insensível a maiúsculas e minúsculas e servidor B sensível a maiúsculas e minúsculas: A sequência de ordenação do servidor A pode não ser sensível a maiúsculas e minúsculas e a sequência de ordenação do servidor B pode ser sensível a maiúsculas e minúsculas. Neste caso, os utilizadores deverão escrever as palavras-passe em maiúsculas depois de transferir os inícios de sessão e as palavras-passe para a instância no servidor B.
    • Servidor A sensível a maiúsculas e minúsculas e servidor B não sensível a maiúsculas e minúsculas: A sequência de ordenação do servidor A pode ser sensível a maiúsculas e minúsculas e a sequência de ordenação do servidor B pode não ser sensível a maiúsculas e minúsculas. Neste caso, os utilizadores não podem iniciar sessão utilizando as autenticações e as palavras-passe que transferiu para a instância no servidor B, a menos que uma das seguintes condições seja verdadeira:
      • As palavras-passe originais não contêm letras.
      • Todas as letras nas palavras-passe originais são letras maiúsculas.
    • Sensível às maiúsculas e minúsculas ou não sensível às maiúsculas e minúsculas em ambos os servidores: A sequência de ordenação do servidor A e do servidor B poderá ser sensível às maiúsculas e minúsculas, ou a sequência de ordenação do servidor A e do servidor B poderá não ser sensível a maiúsculas e minúsculas. Nestes casos, os utilizadores não têm problemas.
  • Um início de sessão que já existia na instância do servidor B poderá ter um nome que é o mesmo que um nome no script de saída. Neste caso, receberá a seguinte mensagem de erro quando executar o script de saída na instância do servidor B:
    Msg 15025, Nível 16, Estado 1, Linha 1
    O 'MyLogin' do servidor Principal já existe.
    Do mesmo modo, um início de sessão que já se encontre na instância do servidor B poderá ter um SID que é igual ao SID no script de saída. Neste caso, receberá a seguinte mensagem de erro quando executar o script de saída na instância do servidor B:
    Msg 15433, Nível 16, Estado 1, Linha 1
    O parâmetro sid fornecido está a ser utilizado.
    Assim, deve efetuar o seguinte:
    1. Reveja cuidadosamente o script de saída.
    2. Examine o conteúdo da vista sys.server_principals na instância do servidor B.
    3. Anote essas mensagens de erro apropriadas.

      No SQL Server 2005, é utilizado o SID para um início de sessão para implementar o acesso ao nível da base de dados. Um início de sessão pode ter SIDs diferentes em bases de dados diferentes num servidor. Neste caso, o início de sessão só pode aceder à base de dados que tem o SID que corresponde ao SID na vista sys.server_principals. Este problema poderá ocorrer se as duas bases de dados forem combinadas a partir de servidores diferentes. Para resolver este problema, remova manualmente o início de sessão da base de dados que tem um erro de correspondência de SID utilizando a instrução DROP USER. Em seguida, adicione novamente o início de sessão utilizando a instrução CREATE USER.
  • Se tentar criar um novo início de sessão SQL Server 2012 utilizando um início de sessão anterior ao SQL Server 2000 em script, receberá a seguinte mensagem de erro:

    Msg 15021, Nível 16, Estado 2, Linha 1
    Valor inválido fornecido para o parâmetro PALAVRA-PASSE. Especifique um valor de parâmetro válido.

    Nota Recebe este erro no SQL Server 2012 devido ao hash de palavra-passe de 16 bytes fornecido para as instruções CREATE LOGIN e ALTER LOGIN.

    Para resolver este problema num servidor que corre o SQL Server 2012, crie um início de sessão que tenha uma palavra-passe em branco. Para o fazer, execute o seguinte script.
    CREATE LOGIN [Test] WITH PASSWORD = '', SID = 0x90FD605DCEFAE14FAB4D5EB0BBA1AECC, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF
    Depois de criar o início de sessão que tem uma palavra-passe em branco, o utilizador pode alterar a palavra-passe na próxima tentativa de início de sessão.

Método 3: Iniciar sessão utilizando a palavra-passe pre-SQL Server 2000

Nota Este método só é relevante se estiver a migrar do SQL Server 2000 para uma versão suportada mais recente do SQL Server.

Nesta situação, peça ao utilizador para iniciar sessão no servidor que corre o SQL Server com o início de sessão pré-SQL Server 2000.

Nota O hash de palavra-passe é atualizado automaticamente quando o utilizador inicia sessão utilizando a palavra-passe pré-SQL Server 2000.

Referências


Para mais informações sobre como resolver problemas de utilizadores órfãos, vá para o Web site Resolução de Problemas de Utilizadores órfãos da Microsoft Developer Network (MSDN).

Para mais informações sobre a instrução CREATE LOGIN, visite o Web site CREATE LOGIN (Transact-SQL) MSDN.

Para mais informações sobre a instrução ALTER LOGIN, vá para o Web site ALTER LOGIN (Transact-SQL) MSDN.