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

Voltar para o início

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

Voltar para o início

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

1. Conectar ao servidor de destino do SQL Server 2000, mover para o Data Transformation Services no SQL Server Enterprise Manager, expanda a pasta, clique Pacotes Local com o botão direito do mouse e, em seguida, clique em Novo pacote .
2. Após os DTS designer abre do pacote, clique em Transferência logons tarefa no menu de tarefas . Preencha as informações sobre as guias de origem , 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 uma instância do SQL Server que esteja em um computador separado, sua instância do SQL Server será deve estar executando em uma conta de domínio para concluir a tarefa.
   
   Observação O método DTS transferirá as senhas, mas não o SID original. Se um logon não é criado usando o SID original e bancos de dados do usuário também são transferidos para um novo servidor, os usuários do banco de dados serão ser órfãos do logon. Para transferir o SID original e ignorar os usuários órfãos, siga as etapas na seção "A resolução concluída a transferência logins e senhas entre versões diferentes do SQL Server".

Voltar para o início

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

Voltar para o início

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

• Os scripts em métodos a seguir criam dois procedimentos armazenados que são nomeados 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. Selecionando diretamente na tabelas do sistema é desencorajado.
• Revise os comentários no final deste artigo para importantes informações sobre as etapas nos métodos.
• Método 2 atribui logons a funções.

Voltar para o início

Método 1

• Você transferir logins e senhas do SQL Server 7.0 ao 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 estão executando o SQL Server 2000.

1. Execute o seguinte script na fonte de SQL Server. Vá para a etapa 2 quando terminar de criar o procedimento sp_help_revlogin armazenados.

   ----- Begin Script, Create sp_help_revlogin procedure -----
   
   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') IS NOT NULL
     DROP PROCEDURE sp_help_revlogin 
   GO
   CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
   DECLARE @name    sysname
   DECLARE @xstatus int
   DECLARE @binpwd  varbinary (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 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_name
   OPEN login_curs 
   FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
   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 '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
     END
   CLOSE login_curs 
   DEALLOCATE login_curs 
   RETURN 0
   GO
    ----- 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 logon que crie logons com o SID original e a senha. Salvar a saída e em seguida, colar e executá-lo no Query Analyzer no SQL Server de destino. Por exemplo:

   EXEC master..sp_help_revlogin
   

Voltar para o início

Método 2

• 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.

1. Execute o seguinte script na fonte de 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 
   GO
   
   exec sp_help_revlogin_2000_to_2005 @login_name=NULL, @include_db=1, @include_role=1
   GO

2. Salvar a saída e em seguida, colar e execute a saída no SQL Server Management Studio no SQL Server 2005 de destino.

CREATE LOGIN LoginName WITH PASSWORD = '', CHECK_POLICY = OFF, SID = MySID

Voltar para o início

Comentários

• Revise o script de saída com cuidado antes de você executá-lo no SQL Server de destino. Se você tiver transferir logins para uma instância do SQL Server em um domínio diferente a instância de origem do SQL Server, edite o script gerado pelo procedimento sp_help_revlogin e substitua o nome de domínio com o novo domínio nas instruções sp_grantlogin . Porque os logins integrados concedido acesso ao novo domínio não irá ter o mesmo SID como os logins no domínio original, os usuários do banco de dados serão ser órfãos desses logons. Para resolver esses usuários órfãos, consulte os artigos citados no seguinte item de marcador. Se você transferir logins integrados entre instâncias do SQL Server no mesmo domínio, o mesmo SID é usado e o usuário não provavelmente ser órfãos.
• Depois de mover os logins, usuários não podem ter permissões para 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 poderá falhar indicando que o usuário já existe:
  Microsoft SQL-DMO (SQLState ODBC: 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 resolver órfãos logins do SQL Server e logins integrados, consulte o seguinte artigo na Base de dados de Conhecimento da Microsoft:
  240872  (http://support.microsoft.com/kb/240872/ ) Como resolver problemas de permissão quando você move um banco de dados entre servidores que estão executando o SQL Server
  Para obter instruções sobre como usar o procedimento armazenado sp_change_users_login para corrigir os usuários órfãos um por um (isso resolverá somente os usuários órfãos a partir de logins SQL), consulte o seguinte artigo na Base de dados de Conhecimento da Microsoft:
  274188  (http://support.microsoft.com/kb/274188/ ) O tópico "Solução de problemas Orphaned usuários" nos manuais online está incompleto
• Se a transferência de logins e senhas fizer parte de uma movimentação de bancos de dados para um novo servidor que executa o SQL Server, consulte o seguinte artigo na Base de dados de Conhecimento da Microsoft para obter uma descrição do fluxo de trabalho e etapas envolvidas:
  314546  (http://support.microsoft.com/kb/314546/ ) Como mover bancos de dados entre computadores que estão executando o SQL Server
• Você pode fazer isso devido a @ encryptopt parâmetro no sistema sp_addlogin procedimento armazenado, que permite que um logon a ser criado usando a senha criptografada. Para obter mais informações sobre esse procedimento, consulte o tópico "sp_addlogin (T-SQL)" nos manuais online do SQL Server.
• Por padrão, somente membros da função servidor sysadminfixed podem selecionar a tabela sysxlogins . A menos que um membro da função sysadmin concede as permissões necessárias, os usuários finais não é possível criar ou executar esses procedimentos armazenados.
• Essa abordagem não tenta transferir as informações de banco de dados padrão para um logon específico porque o banco de dados padrão não pode sempre existe no servidor de destino. Para definir o banco de dados padrão para um logon, você pode usar o procedimento armazenado do sistema de sp_defaultdb passando-o nome de logon e o banco de dados padrão 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 é não diferencia maiúsculas de minúsculas e a ordem de classificação do servidor de destino é diferencia maiúsculas de minúsculas, você deve digitar todos os caracteres alfabéticos em senhas como caracteres maiúsculos após a transferência de logons para o servidor de destino. 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 é não diferencia maiúsculas de minúsculas, não será possível fazer logon com os logins transferidos usando o procedimento descrito neste artigo, a menos que a senha original contém caracteres não alfabéticos ou a menos que todos os caracteres alfabéticos na senha original caracteres maiúsculos. Se ambos os servidores diferenciam maiúsculas de minúsculas ou ambos os servidores não diferencia maiúsculas de minúsculas, não terão esse problema. Isso é um efeito colateral da maneira que o SQL Server lida com senhas. Para obter mais informações, consulte o tópico "Efeito em senhas de alterar ordens de classificação" 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 definido com o mesmo nome de um dos logons na saída do script de logon, você pode ver o seguinte erro durante a execução da saída do script sp_help_revlogin :
  Servidor: Mensagem 15025, nível 16, estado 1, procedimento sp_addlogin, linha 56
  O logon 'test1' já existe.
  Da mesma forma, se existir um logon diferente com o mesmo valor SID neste servidor que você está tentando adicionar, você receber a seguinte mensagem de erro:
  Mensagem de servidor: 15433, nível 16, estado 1, procedimento sp_addlogin, linha 93
  O parâmetro fornecido @ sid está em uso.
  Portanto, você deve cuidadosamente revisar o resultado desses comandos, examinar o conteúdo da tabela sysxlogins e resolver esses erros adequadamente.
• O valor de SID para um logon específico é usado como base para implementar acesso em nível de banco de dados no SQL Server. Portanto, se o mesmo logon tem dois valores diferentes para o SID no nível de banco de dados (dois bancos de diferentes dados no servidor), o logon apenas terão acesso a esse banco de dados cujo SID corresponde ao valor no syslogins para esse logon. Tal situação pode ocorrer se os dois bancos de dados em questão tiverem sido consolidados de dois servidores diferentes. Para resolver esse problema, o logon em questão teria que ser removidos manualmente do banco de dados que possui uma incompatibilidade de SID usando o procedimento sp_dropuser armazenados e, em seguida, adicionado novamente usando o procedimento armazenado sp_adduser .

Voltar para o início