Como transferir logons e senhas 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 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.

Para obter mais informações sobre como transferir os logons e as senhas entre as instâncias de outras versões do SQL Server, clique no número do artigo correspondente para visualizá-lo na Base de Dados de Conhecimento da Microsoft:

246133 Como transferir logons e senhas entre instâncias do SQL Server que executam versões mais antigas do SQL Server

Informações adicionais


Neste artigo, o servidor A e B são diferentes. 
 
Depois de mover o banco de dados de uma instância do SQL Server no servidor A para a instância do SQL Server no servidor B, os usuários podem não ser capazes de fazer logon no banco de dados do servidor B. Além disso, os usuários podem receber a seguinte mensagem de erro:
Falha no logon para o usuário 'MyUser'. (Microsoft SQL Server, Erro: 18456)
Este problema ocorre porque você não transferiu os logons e senhas da instância do SQL Server no servidor A para o servidor B.

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

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

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

Observação O algoritmo de hash da senha é usado quando você redefine a senha.

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

Para criar um log no script com uma senha em branco, siga estas etapas:
  1. No servidor A, inicie o SQL Server Management Studio e conecte na instância do SQL Server a partir do qual você moveu o banco de dados.
  2. Abra uma nova janela do Editor de Consultas 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 Este script cria dois procedimentos armazenados no banco de dados mestre. Os procedimentos são denominados sp_hexadecimal e sp_help_revlogin.
  3. Execute a seguinte instrução na mesma janela de consulta ou em uma nova janela de consulta: 
    EXEC sp_help_revlogin
    O script de saída gerado pelo procedimento armazenado sp_help_revlogin é o script do logon. Este script de logon cria os logons que tem o Identificador de Segurança (SID) e a senha original.

 Etapas no servidor de destino (Servidor B):

  1. No servidor B, inicie o SQL Server Management Studio e conecte à instância do SQL Server para a qual você moveu o banco de dados.

    Importante Antes de ir para a etapa 2, revise as informações na seção "Comentários" abaixo.
  2. Abra uma nova janela do Editor de consultas e execute o script de saída que é gerado na etapa 2 do procedimento anterior.

Comentários

Revise a seguinte informação antes de executar o script de saída na instância no servidor B:

  • Uma senha pode conter hash das seguintes maneiras:
    • VERSION_SHA1: Esse hash é gerado pelo 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.
  • Reveja o script de saída cuidadosamente. Se o servidor A e B estiverem em domínios diferentes, você terá que modificar o script de saída. Em seguida, você deverá substituir o nome do domínio original com o nome do novo domínio na instrução CRIAR LOGON. Os logons integrados que tem acesso garantido no novo domínio não tem o mesmo SID como os logons do domínio original. Portanto, os usuários ficam órfãos desses logons. Para obter mais informações sobre como resolver estes usuários órfãos, clique no número do artigo correspondente para visualizá-lo na Base de Dados de Conhecimento Microsoft:

    240872 Como resolver problemas de permissão ao mover o banco de dados entre os servidores que estão executando o SQL Server

    Se o servidor A e B estão no mesmo domínio, o mesmo SID é usado. Portanto, os usuários provavelmente não ficarão órfãos.
  • No script de saída, os logons são criados usando a senha criptografada. Isto é por causa do argumento HASHED na instrução CRIAR LOGON. Este argumento especifica que a senha que foi inserida depois do argumento SENHA já foi transformada em hash.
  • Por padrão, somente um membro da função do servidor fixo sysadmin pode executar uma instrução SELECT da visualização sys.server_principals. Pelo menos um membro da função do servidor fixo sysadmin concede 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 a informação do banco de dados padrão para um logon particular. Isto é porque o banco de dados pode não existir sempre no servidor B. Para definir o banco de dados padrão para um logon, use a instrução ALTER LOGIN passando no nome do logon e no banco de dados padrão como argumentos.
  • Classificar ordens nos servidores de origem e de destino:
    • Servidor A que não diferencia maiúsculas de minúsculas e servidor B que diferencia maiúsculas de minúsculas: A ordem de classificação do servidor A pode não diferenciar maiúscula de minúscula, e a ordem de classificação do servidor B pode diferenciar maiúscula de minúscula. Neste caso, os usuários devem digitar todas as letras nas senhas como letras maiúsculas depois de transferir os logons e as senhas para a instância no servidor B.
    • Servidor A que diferencia maiúsculas de minúsculas e servidor B sem diferenciação de maiúsculas e minúsculas: A ordem de classificação do servidor A pode diferenciar maiúscula de minúscula, e a ordem de classificação do servidor B pode não diferenciar maiúscula de minúscula. Neste caso, os usuários não podem fazer logon usando os logons e as senhas que transferem 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 maiúsculas.
    • Diferencia ou não diferencia maiúsculas e minúsculas em ambos os servidores: A ordem de classificação do servidor A e B pode diferenciar maiúsculas de minúsculas ou a ordem de classificação do servidor A e B pode não diferenciar maiúsculas de minúsculas. Nestes casos, os usuários não experimentam um problema.
  • Um logon que já está na instância no servidor B pode ter um nome igual ao nome no script de saída. Neste caso, você recebe a seguinte mensagem de erro ao executar o script de saída na instância no servidor B:
    Msg 15025, Nível 16, Estado 1, Linha 1
    A entidade do servidor 'MyLogin' já existe.
    Da mesma forma, um logon que já existe na instância no servidor B pode ter um SID que é o mesmo do que um SID no script de saída. Neste caso, você recebe 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
    O sid do parâmetro fornecido está em uso.
    Portanto, você deve fazer o seguinte:
    1. Reveja o script de saída cuidadosamente.
    2. Examine os conteúdos da exibição sys.server_principals na instância no servidor B.
    3. Trate estas mensagens de erro conforme adequado.

      No SQL Server 2005, o SID para um logon é usado para implementar o acesso de nível do banco de dados. Um logon pode ter dois SIDs diferentes em dois bancos de dados diferentes em um servidor. Neste caso, o logon somente pode acessar o banco de dados que tenha o SID que combine com o SID na exibição do sys.server_principals. Este problema poderá ocorrer se os dois bancos de dados forem combinados por meio de servidores diferentes. Para resolver este problema, remova manualmente o logon do banco de dados que tem um SID incompatível usando a instrução REMOVER USUÁRIO. Em seguida, adicione o logon novamente usando a instrução CRIAR USUÁRIO.
  • Caso tente criar um novo logon do SQL Server 2012 usando um logon pré-SQL Server 2000 que seja com script, você receberá a seguinte mensagem de erro:

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

    Observação Você recebe esse erro no SQL Server 2012 devido ao hash de senha de 16 bytes fornecido para as instruções CREATE LOGIN e ALTER LOGIN.

    Para resolver esse problema em um servidor que está executando o SQL Server 2012, crie um logon que tenha uma senha em branco. Para fazer isso, execute o seguinte script:
    CREATE LOGIN [Test] WITH PASSWORD = '', SID = 0x90FD605DCEFAE14FAB4D5EB0BBA1AECC, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF
    Depois de criar o logon com uma senha em branco, o usuário poderá alterar a senha na próxima tentativa de logon.

Método 3: Faça logon usando a senha do pré-SQL Server 2000

Observação Esse método será relevante somente se você estiver migrando o SQL Server 2000 para uma versão compatível mais recente do SQL Server.

Nessa situação, peça ao usuário que faça logon no servidor que está executando o SQL Server usando o logon do pré-SQL Server 2000.

Observação O hash da senha é atualizado automaticamente quando o usuário faz logon usando a senha do pré-SQL Server 2000.

Referências


Para obter mais informações sobre como resolver problemas de usuários órfãos, acesse Solucionar problemas de usuários órfãos no site do Microsoft Developer Network (MSDN).

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

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