Você está offline; aguardando reconexão

Como transferir logins e senhas entre instâncias do SQL Server

IMPORTANTE: Este artigo foi traduzido pelo software de tradução automática da Microsoft e eventualmente pode ter sido editado pela Microsoft Community através da tecnologia Community Translation Framework (CTF) ou por um tradutor profissional. A Microsoft oferece artigos traduzidos automaticamente por software, por tradutores profissionais e editados pela comunidade para que você tenha acesso a todos os artigos de nossa Base de Conhecimento em diversos idiomas. No entanto, um artigo traduzido pode conter erros de vocabulário, sintaxe e/ou gramática. A Microsoft não é responsável por qualquer inexatidão, erro ou dano causado por qualquer tradução imprecisa do conteúdo ou por seu uso pelos nossos clientes.

Clique aqui para ver a versão em Inglês deste artigo: 246133
Sumário
Depois que você mover bancos de dados para um novo servidor, os usuários não poderá fazer logon novo servidor. Em vez disso, eles recebem a seguinte mensagem de erro:
Msg 18456, nível 16, estado 1
Falha de logon do usuário '%ls'.
Você deve transferir os logins e senhas para o novo servidor. Este artigo descreve como transferir logins e senhas para um novo servidor.

Como transferir logins e senhas entre servidores que estejam executando o SQL Server 7.0

O recurso de transferência de objeto do SQL Server 7.0 Data Transformation Services (DTS) transfere logons e usuários entre dois servidores, mas não transfere as senhas para logins do SQL Server autenticado. Para transferir logins e senhas de um servidor que esteja executando o SQL Server 7.0 para outro servidor que esteja executando o SQL Server 7.0, siga as etapas na seção "Uma solução completa para transferir logins e senhas entre versões diferentes do SQL Server".

Como transferir logins e senhas do SQL Server 7.0 para o SQL Server 2000 ou entre servidores que estejam executando o SQL Server 2000

Para transferir logins e senhas de um servidor do SQL Server 7.0 para uma instância do SQL Server 2000 ou entre duas instâncias do SQL Server 2000, você pode usar a nova tarefa de Logins de transferência de pacote DTS no SQL Server 2000. Para fazer isso, execute as seguintes etapas:
  1. Conectar-se ao servidor de destino SQL Server 2000, mover para o Data Transformation Services no SQL Server Enterprise Manager, pasta expandthe, clique com botão direito Pacotes locaise, em seguida, clique em Novo pacote.
  2. Depois do pacote do DTS designer abre, clique em Transferir Logins de tarefas no menu tarefa . Preencha as informações sobre as guias de origem, no destino e logons conforme apropriado.

    Importante: O servidor de destino do SQL Server 2000 não pode estar executando a versão de 64 bits do SQL Server 2000. Componentes DTS para a versão de 64 bits do SQL Server 2000 não estão disponíveis. Se você estiver importando logons de um SQL Server que esteja em um computador separado instanceof, sua instância do SQL Server willmust ser executado sob uma conta de domínio para concluir a tarefa.

    Observação: O método DTS transferirá as senhas, mas não theoriginal SID. Se um logon não é criado usando o SID original e userdatabases também são transferidos para um novo servidor, os usuários do banco de dados serão beorphaned na página de login. Para transferir o SID original e ignorar a orphanedusers, siga as etapas na seção "Uma solução completa para transferir logins e senhas entre versões diferentes do SQL Server".

Como transferir logins e senhas entre instâncias do SQL Server 2005

Para obter mais informações sobre como transferir logins e senhas entre instâncias do SQL Server 2005, clique no número abaixo para ler o artigo na Base de dados de Conhecimento Microsoft:
918992 Como transferir logins e senhas entre instâncias do SQL Server 2005

Uma solução completa para transferir logins e senhas entre versões diferentes do SQL Server

Para fazer isso, use um dos seguintes métodos.
Observação
  • Os scripts nos seguintes métodos criam dois procedimentos armazenados que são nomeados o procedimento sp_hexadecimal armazenado e o procedimento sp_help_revlogin armazenados no seu banco de dados mestre .
  • Os scripts são dependentes em tabelas de sistema do SQL Server.A estrutura dessas tabelas pode alterar entre versões do SQL Server. Não é recomendado selecionar diretamente das tabelas de sistema.
  • Revise os comentários no final deste artigo para obter informações importantes sobre as etapas nos métodos.
  • Método 2 atribui logons para funções.

Método 1

Esse método se aplica para os seguintes cenários:
  • Você transferir logins e senhas do SQL Server 7.0 para o SQL Server 7.0.
  • Você transferir logins e senhas do SQL Server 7.0 para o SQL Server 2000.
  • Você transferir logins e senhas entre servidores que estejam executando o SQL Server 2000.
Para transferir logins e senhas entre versões diferentes do SQL Server, execute estas etapas:
  1. Execute o seguinte script na origem do SQL Server. Vá para a etapa 2 quando você terminar de criar o procedimentosp_help_revlogin armazenados.
    ----- Begin Script, Create sp_help_revlogin procedure -----USE masterGOIF OBJECT_ID ('sp_hexadecimal') IS NOT NULL  DROP PROCEDURE sp_hexadecimalGOCREATE PROCEDURE sp_hexadecimal    @binvalue varbinary(256),    @hexvalue varchar(256) OUTPUTASDECLARE @charvalue varchar(256)DECLARE @i intDECLARE @length intDECLARE @hexstring char(16)SELECT @charvalue = '0x'SELECT @i = 1SELECT @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 + 1ENDSELECT @hexvalue = @charvalueGOIF OBJECT_ID ('sp_help_revlogin') IS NOT NULL  DROP PROCEDURE sp_help_revlogin GOCREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL ASDECLARE @name    sysnameDECLARE @xstatus intDECLARE @binpwd  varbinary (256)DECLARE @txtpwd  sysnameDECLARE @tmpstr  varchar (256)DECLARE @SID_varbinary varbinary(85)DECLARE @SID_string varchar(256)IF (@login_name IS NULL)  DECLARE login_curs CURSOR FOR     SELECT sid, name, xstatus, password FROM master..sysxlogins     WHERE srvid IS NULL AND name <> 'sa'ELSE  DECLARE login_curs CURSOR FOR     SELECT sid, name, xstatus, password FROM master..sysxlogins     WHERE srvid IS NULL AND name = @login_nameOPEN login_curs FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwdIF (@@fetch_status = -1)BEGIN  PRINT 'No login(s) found.'  CLOSE login_curs   DEALLOCATE login_curs   RETURN -1ENDSET @tmpstr = '/* sp_help_revlogin script ' PRINT @tmpstrSET @tmpstr = '** Generated '   + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'PRINT @tmpstrPRINT ''PRINT 'DECLARE @pwd sysname'WHILE (@@fetch_status <> -1)BEGIN  IF (@@fetch_status <> -2)  BEGIN    PRINT ''    SET @tmpstr = '-- Login: ' + @name    PRINT @tmpstr     IF (@xstatus & 4) = 4    BEGIN -- NT authenticated account/group      IF (@xstatus & 1) = 1      BEGIN -- NT login is denied access        SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''        PRINT @tmpstr       END      ELSE BEGIN -- NT login has access        SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''        PRINT @tmpstr       END    END    ELSE BEGIN -- SQL Server authentication      IF (@binpwd IS NOT NULL)      BEGIN -- Non-null password        EXEC sp_hexadecimal @binpwd, @txtpwd OUT        IF (@xstatus & 2048) = 2048          SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'        ELSE          SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'        PRINT @tmpstr	EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT        SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name           + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '      END      ELSE BEGIN         -- Null password	EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT        SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name           + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '      END      IF (@xstatus & 2048) = 2048        -- login upgraded from 6.5        SET @tmpstr = @tmpstr + '''skip_encryption_old'''       ELSE         SET @tmpstr = @tmpstr + '''skip_encryption'''      PRINT @tmpstr     END  END  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd  ENDCLOSE login_curs DEALLOCATE login_curs RETURN 0GO ----- End Script -----
  2. Depois de criar o procedimento sp_help_revlogin armazenados, execute o procedimento sp_help_revlogin do analisador de consultas no servidor de origem. O procedimento sp_help_revlogin armazenados pode ser usado no SQL Server 7.0 e SQL Server 2000. A saída do procedimento sp_help_revlogin armazenados é scripts de login que criar logons com theoriginal SID e da senha. Salvar a saída e cole e executá-la no QueryAnalyzer no destino do SQL Server. Por exemplo:
    EXEC master..sp_help_revlogin

Método 2

Esse método se aplica para os seguintes cenários:
  • Você transferir logins e senhas do SQL Server 7.0 para o SQL Server 2005.
  • Você transferir logins e senhas do SQL Server 2000 para o SQL Server 2005.
  • Você pode atribuir logons para funções.
Para transferir logins e senhas entre versões diferentes do SQL Server e, em seguida, atribuir logons para funções, siga estas etapas:
  1. Execute o seguinte script na origem do SQL Server.
    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(256) OUTPUT AS DECLARE @charvalue varchar(256) 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_2000_to_2005') IS NOT NULL DROP PROCEDURE sp_help_revlogin_2000_to_2005 GO CREATE PROCEDURE sp_help_revlogin_2000_to_2005 @login_name sysname = NULL, @include_db bit = 0, @include_role bit = 0 AS DECLARE @name sysname DECLARE @xstatus int DECLARE @binpwd varbinary (256) DECLARE @dfltdb varchar (256) DECLARE @txtpwd sysname DECLARE @tmpstr varchar (256) DECLARE @SID_varbinary varbinary(85) DECLARE @SID_string varchar(256) IF (@login_name IS NULL) DECLARE login_curs CURSOR STATIC FOR SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master') FROM master.dbo.sysxlogins WHERE srvid IS NULL AND [name] <> 'sa' ELSE DECLARE login_curs CURSOR FOR SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master') FROM master.dbo.sysxlogins WHERE srvid IS NULL AND [name] = @login_name OPEN login_curs FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb 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 '' PRINT '' PRINT '' PRINT '/***** CREATE LOGINS *****/' WHILE @@fetch_status = 0 BEGIN PRINT '' SET @tmpstr = '-- Login: ' + @name PRINT @tmpstr IF (@xstatus & 4) = 4 BEGIN -- NT authenticated account/group IF (@xstatus & 1) = 1 BEGIN -- NT login is denied access SET @tmpstr = '' --'EXEC master..sp_denylogin ''' + @name + '''' PRINT @tmpstr END ELSE BEGIN -- NT login has access SET @tmpstr = 'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = ''' + @name + ''')' PRINT @tmpstr SET @tmpstr = CHAR(9) + 'CREATE LOGIN [' + @name + '] FROM WINDOWS' PRINT @tmpstr END END ELSE BEGIN -- SQL Server authentication EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT IF (@binpwd IS NOT NULL) BEGIN -- Non-null password EXEC sp_hexadecimal @binpwd, @txtpwd OUT SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD=' + @txtpwd + ' HASHED' END ELSE BEGIN -- Null password SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD=''''' END SET @tmpstr = @tmpstr + ', CHECK_POLICY=OFF, SID=' + @SID_string PRINT @tmpstr END FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb END IF @include_db = 1 BEGIN PRINT '' PRINT '' PRINT '' PRINT '/***** SET DEFAULT DATABASES *****/' FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb WHILE @@fetch_status = 0 BEGIN PRINT '' SET @tmpstr = '-- Login: ' + @name PRINT @tmpstr SET @tmpstr = 'ALTER LOGIN [' + @name + '] WITH DEFAULT_DATABASE=[' + @dfltdb + ']' PRINT @tmpstr FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb END END IF @include_role = 1 BEGIN PRINT '' PRINT '' PRINT '' PRINT '/***** SET SERVER ROLES *****/' FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb WHILE @@fetch_status = 0 BEGIN PRINT '' SET @tmpstr = '-- Login: ' + @name PRINT @tmpstr IF @xstatus &16 = 16 -- sysadmin BEGIN SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''sysadmin''' PRINT @tmpstr END IF @xstatus &32 = 32 -- securityadmin BEGIN SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''securityadmin''' PRINT @tmpstr END IF @xstatus &64 = 64 -- serveradmin BEGIN SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''serveradmin''' PRINT @tmpstr END IF @xstatus &128 = 128 -- setupadmin BEGIN SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''setupadmin''' PRINT @tmpstr END IF @xstatus &256 = 256 --processadmin BEGIN SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''processadmin''' PRINT @tmpstr END IF @xstatus &512 = 512 -- diskadmin BEGIN SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''diskadmin''' PRINT @tmpstr END IF @xstatus &1024 = 1024 -- dbcreator BEGIN SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''dbcreator''' PRINT @tmpstr END IF @xstatus &4096 = 4096 -- bulkadmin BEGIN SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''bulkadmin''' PRINT @tmpstr END FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb END END CLOSE login_curs DEALLOCATE login_curs RETURN 0 GOexec sp_help_revlogin_2000_to_2005 @login_name=NULL, @include_db=1, @include_role=1GO
  2. Salvar a saída, colar e executar a saída no SQL Server Management Studio no SQL Server 2005 do destino.
Observação: Se o SQL Server de origem contiver um logon que tenha uma senha em branco, a saída contém uma instrução semelhante à seguinte.
CREATE LOGIN LoginName WITH PASSWORD = '', CHECK_POLICY = OFF, SID = MySID

Comentários

  • Revise o script de saída com atenção antes de executá-la no thedestination do SQL Server. Se você precisar transferir logins para uma instância do SQL Server em um domínio diferente a instância de origem do SQL Server, edite thescript gerado pelo procedimento sp_help_revlogin e substitua o nome do domínio no novo domínio nas instruçõessp_grantlogin . Porque os logins integrados que receberam acesso no novo domínio não terá o mesmo SID como os logons no domínio original, os usuários do banco de dados estarão órfãos destes inícios de sessão. Para resolver esses orphanedusers, consulte os artigos citados no item de marcador a seguir. Se youtransfer integrado logons entre instâncias do SQL servidores no mesmo domínio, o mesmo SID é usado e o usuário não é provável se tornar órfão.
  • Depois que você mover os logons, usuários podem não ter permissionsto acessar bancos de dados que também foram movidos. Esse problema é descrito como um "usuário órfão". Se você tentar conceder o acesso de logon ao banco de dados, ele mayfail indicando o usuário já existe:
    Microsoft SQL-DMO (ODBC SQLState: 42000) erro 15023: usuário ou função '%s' já existe no banco de dados atual.
    Para obter instruções sobre como mapear os logins para os usuários do banco de dados para resolver órfãos logins do SQL Server e logins integrados, consulte o seguinte artigo da Base de dados de Conhecimento da Microsoft:
    240872 Como resolver problemas de permissão ao mover um banco de dados entre servidores que estejam executando o SQL Server
    Para obter instruções sobre como usar o sp_change_users_login armazenados procedimento para corrigir os usuários órfãos, um por um (thiswill somente endereço usuários órfãos a partir de logins SQL padrão), consulte o followingarticle da Base de dados de Conhecimento da Microsoft:
    274188 O tópico "Solucionando problemas de usuários Orphaned" nos Manuais Online está incompleto
  • Se a transferência de logons e senhas fizer parte de um banco de dados moveof para um novo servidor executando o SQL Server, consulte o seguinte artigo no Microsoft Knowledge Base para obter uma descrição do fluxo de trabalho e stepsinvolved:
    314546 Como mover bancos de dados entre computadores que estejam executando o SQL Server
  • Você pode fazer isso por causa do parâmetro @encryptopt no sp_addlogin procedimento armazenado do sistema, que permite que um logon criado finalatravés a senha criptografada. Para obter mais informações sobre esse procedimento, seethe o tópico "sp_addlogin (T-SQL)" nos Manuais Online do SQL Server.
  • Por padrão, somente os membros do rolecan de servidor de sysadminfixed selecione da tabela sysxlogins . A menos que um membro da função sysadmin concede a necessarypermissions, os usuários finais não pode criar ou executar esses procedimentos armazenados.
  • Essa abordagem não tenta transferir a padrão de instalaçãoAs informações de um logon específico porque o banco de dados padrão pode não alwaysexist no servidor de destino. Para definir o banco de dados padrão para um logon, você pode usar o procedimento armazenado do sistema sp_defaultdb , passando-os do banco de dados do login nome e thedefault como argumentos. Para obter mais informações sobre como usar este procedimento, consulte o tópico "sp_defaultdb" nos Manuais Online do SQL Server.
  • Durante uma transferência de logons entre instâncias do SQL Server, se a ordem de classificação do servidor de origem diferencia maiúsculas de minúsculas e a ordem de classificação do servidor de destino diferencia maiúsculas de minúsculas, digite allalphabetical caracteres em senhas como caracteres em maiúsculas após os logons de transferof para o servidor de destino. Se a ordem de classificação de diferenciação de iscase o servidor fonte e a ordem de classificação do servidor de destino não levem em conta a iscase, você não poderá fazer logon com o transferredusing de logins o procedimento descrito neste artigo, a menos que o passwordcontains original sem caracteres alfabéticos ou a menos que todos os caracteres alfabéticos na senha original são caracteres maiúsculos. Se os servidores arecase-sensível ou ambos os servidores diferenciam maiúsculas de minúsculas, você irá experiencethis não é problema. Esse é um efeito colateral da maneira que handlespasswords do SQL Server. Para obter mais informações, consulte o tópico "Efeito em senhas de alteração SortOrders" nos Manuais Online do SQL Server 7.0.
  • Quando você executa a saída do script sp_help_revlogin no servidor de destino, se o servidor de destino já tiver um login definedwith o mesmo nome que um dos logins na saída do script, você pode ver asseguintes erro durante a execução da saída do script sp_help_revlogin :
    Servidor: Msg 15025, nível 16, estado 1, procedimento sp_addlogin, linha 56
    O login 'test1' já existe.
    Da mesma forma, se já houver um logon diferente com o mesmo valor de SID este servidor como um que está tentando adicionar, receivethe seguinte mensagem de erro:
    Servidor: Msg15433, nível 16, estado 1, procedimento sp_addlogin, linha 93
    Suppliedparameter @sid está em uso.
    Portanto, você deve carefullyreview a saída desses comandos, examine o conteúdo da tabela sysxlogins e endereço esses erros adequadamente.
  • O valor de SID para um logon específico é usado como o basisfor implementar acesso em nível de banco de dados no SQL Server. Portanto, se o samelogin tem dois valores diferentes para o SID no nível do banco de dados (em bancos de dados de twodifferent no servidor), o logon só terá acesso ao thatdatabase cujo SID corresponde ao valor no syslogins para esse logon. Essa situação pode ocorrer se a pergunta de dois databasesin foram consolidada de dois servidores diferentes. Para resolver thisproblem, o login em questão precisam ser removidos manualmente do banco de dados que tem uma incompatibilidade de SID, utilizando o procedimento sp_dropuser armazenados e adicionada novamente usando o procedimento armazenado sp_adduser .

Aviso: este artigo foi traduzido automaticamente

Propriedades

ID do Artigo: 246133 - Última Revisão: 08/20/2014 18:32:00 - Revisão: 10.0

Microsoft SQL Server 7.0 Standard Edition, Microsoft SQL Server 2000 Personal Edition, Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 2000 Workgroup Edition, Microsoft SQL Server 2000 Developer Edition, Microsoft SQL Server 2000 Enterprise Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Workgroup Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition

  • kbsqlsetup kbhowtomaster kbinfo kbmt KB246133 KbMtpt
Comentários
/html>