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

Como transferir os inícios de sessão e palavras-passe entre servidores que executem o SQL Server 7.0

A transferir os inícios de sessão e palavras-passe do SQL Server 7.0 para o SQL Server 2000 ou entre servidores que executem o SQL Server 2000

1. Ligar ao servidor de destino do SQL Server 2000, mover para os serviços de transformação de dados no SQL Server Enterprise Manager, expanda a pasta, clique com o botão direito do rato Pacotes Local e, em seguida, clique em Novo pacote .
2. Depois dos DTS compactar designer abre, clique em Tarefas de inícios de sessão de transferência no menu de tarefas . Preencha as informações sobre os separadores de origem , destino e inícios de sessão conforme apropriado.
   
   importante Servidor de destino do SQL Server 2000 não pode estar a executar a versão de 64 bits do SQL Server 2000. Não existem componentes DTS para a versão de 64 bits do SQL Server 2000. Se estiver a importar os inícios de sessão a partir de uma instância do SQL Server num computador diferente, a instância do SQL Server irá tem estar em execução sob uma conta de domínio para concluir a tarefa.
   
   Nota O método DTS transferirá as palavras-passe mas não o SID do original. Se um início de sessão não é criado utilizando o SID original e bases de dados de utilizador também são transferidas para um novo servidor, os utilizadores da base de dados vão ser isolados a partir do início de sessão. Para transferir o SID original e ignorar os utilizadores isolados, siga os passos na secção "De resolução concluída a transferência de inícios de sessão e palavras-passe entre diferentes versões do SQL Server".

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

Uma solução completa para transferir os inícios de sessão e palavras-passe entre diferentes versões do SQL Server

• Os scripts em métodos que se seguem criar dois procedimentos armazenados são denominados procedimento sp_hexadecimal armazenados e o procedimento sp_help_revlogin armazenados na base de dados principal .
• Os scripts estão dependentes de tabelas de sistema do SQL Server. Estrutura destas tabelas pode mudar entre as versões do SQL Server. Não seleccionar directamente de tabelas de sistema é aconselhada.
• Rever os comentários de no fim deste artigo para obter informações importantes sobre os passos os métodos.
• Método 2 atribui os inícios de sessão a funções.

Método 1

• Transferir os inícios de sessão e palavras-passe do SQL Server 7.0 para o SQL Server 7.0.
• Transferir os inícios de sessão e palavras-passe do SQL Server 7.0 para o SQL Server 2000.
• Transferir os inícios de sessão e palavras-passe entre servidores que executem o SQL Server 2000.

1. Execute o seguinte script na origem do SQL Server. Continuam até ao passo 2 quando acabar 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 armazenadas, execute o procedimento sp_help_revlogin do analisador de consultas no servidor de origem. O procedimento armazenado sp_help_revlogin pode ser utilizado no SQL Server 7.0 e SQL Server 2000. O resultado do procedimento armazenado sp_help_revlogin é scripts de início de sessão que criam os inícios de sessão com o SID original e a palavra-passe. Guardar a saída e, em seguida, colar e executá-la no Query Analyzer no destino do SQL Server. Por exemplo:

   EXEC master..sp_help_revlogin
   

Método 2

• Transferir os inícios de sessão e palavras-passe do SQL Server 7.0 para o SQL Server 2005.
• Transferir os inícios de sessão e palavras-passe do SQL Server 2000 para o SQL Server 2005.
• Inícios de sessão pode atribuir a funções.

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 
   GO
   
   exec sp_help_revlogin_2000_to_2005 @login_name=NULL, @include_db=1, @include_role=1
   GO

2. Guardar o resultado, em seguida, colar e executar a saída no SQL Server Management Studio no destino SQL Server 2005.

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

Comentários

• Reveja cuidadosamente o script de saída antes de executar no destino do SQL Server. Se tiver de transferir os inícios de sessão para uma instância do SQL Server num domínio diferente do que 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 . Uma vez que os inícios de sessão integrados concedido acesso no novo domínio não vai ter o SID do mesmo como os inícios de sessão no domínio original, os utilizadores da base de dados irão ficar isolados destes inícios de sessão. Para resolver estes utilizadores isolados, consulte os artigos referenciados no seguinte item de marca. Se transferir inícios de sessão integrados entre instâncias do SQL Server no mesmo domínio, o SID do mesmo é utilizado e o utilizador não é provável que ficar isolado.
• Depois de mover os inícios de sessão, os utilizadores poderão não ter permissões para aceder a bases de dados que também tenham sido movidas. Este problema é descrito como "utilizador órfão". Se tentar conceder acesso de início de sessão na base de dados, poderá falhar indicando que o utilizador já existe:
  Microsoft SQL-DMO (SQLState ODBC: 42000) erro 15023: utilizador ou função '% s' já existe na base de dados actual.
  Para obter instruções sobre como mapear os inícios de sessão aos utilizadores da base de dados resolver isolados inícios de sessão do SQL Server e inícios de sessão 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ões quando move uma base de dados entre servidores que executem o SQL Server
  Para obter instruções sobre como utilizar o procedimento armazenado sp_change_users_login para corrigir os utilizadores isolados--individualmente (Isto só resolverá utilizadores isolados de inícios de sessão SQL padrão), consulte o seguinte artigo na base de dados de conhecimento da Microsoft:
  274188

  (http://support.microsoft.com/kb/274188/ )

  Tópico "Resolução de problemas de utilizadores Orphaned" no Books Online está incompleto
• Se a transferência de inícios de sessão e palavras-passe fizer parte de uma jogada de bases de dados para um novo servidor com o SQL Server, consulte o seguinte artigo na Microsoft Knowledge Base para obter uma descrição do fluxo de trabalho e os passos envolvidos:
  314546

  (http://support.microsoft.com/kb/314546/ )

  Como mover bases de dados entre computadores com o SQL Server
• Pode fazê-lo devido @ encryptopt parâmetro no sistema sp_addlogin armazenados procedimento, o que permite a um login de ser criados utilizando a palavra-passe encriptada. Para mais informações sobre este procedimento, consulte o tópico "sp_addlogin (T-SQL)" no SQL Server Books Online.
• Por predefinição, podem seleccionar apenas os membros da função servidor sysadminfixed da tabela sysxlogins . A menos que um membro da função sysadmin concede as permissões necessárias, os utilizadores finais não é possível criar ou executar estes procedimentos armazenados.
• Esta abordagem não tenta transferir as informações de base de dados predefinido para um início de sessão específico, porque a base de dados predefinido não poderá sempre existe no servidor de destino. Para definir a base de dados predefinido para um início de sessão, pode utilizar o procedimento armazenado do sistema de sp_defaultdb por transmitir o nome de início de sessão e a base de dados predefinido como argumentos. Para mais informações sobre como utilizar este procedimento, consulte o tópico "sp_defaultdb" no SQL Server Books Online.
• Durante a transferência de inícios de sessão entre instâncias do SQL Server, se a sequência de ordenação do servidor de origem não sensível a maiúsculas e minúsculas e a sequência de ordenação do servidor de destino é sensível a maiúsculas e minúsculas, tem de introduzir todos os caracteres alfabéticos em palavras-passe como caracteres em maiúsculas depois da transferência de inícios de sessão ao servidor de destino. Se a sequência de ordenação do servidor de origem é sensível a maiúsculas e minúsculas e a sequência de ordenação do servidor de destino é não sensível a maiúsculas e minúsculas, não conseguirá iniciar sessão com os logins transferidos utilizando o procedimento descrito neste artigo, a menos que a palavra-passe original contém caracteres não alfabéticos ou se todos os caracteres alfabéticos, a palavra-passe original são caracteres em maiúsculas. Se ambos os servidores são sensíveis a maiúsculas e minúsculas ou ambos os servidores são sensíveis a maiúsculas e minúsculas, não detectará este problema. Este é um efeito secundário de forma como o SQL Server processa as palavras-passe. Para mais informações, consulte o tópico "Efeito em palavras-passe de alterar ordenação encomendas" no SQL Server 7.0 Books Online.
• Quando executa o resultado do script sp_help_revlogin no servidor de destino, se o servidor de destino já tiver um início de sessão definido com o mesmo nome dos inícios de sessão no resultado do script, poderá ver o seguinte erro durante a execução do resultado do script sp_help_revlogin :
  Servidor: Erro 15025, 16 de nível 1 de estado, procedimento sp_addlogin, linha 56
  O início de sessão 'Teste1' já existe.
  Do mesmo modo, se existir um início de sessão diferente com o mesmo valor de SID neste servidor que está a tentar adicionar, receberá a seguinte mensagem de erro:
  Erro de servidor: 15433, 16 de nível 1 de estado, sp_addlogin procedimento, linha 93
  Fornecido parâmetro @ sid está a ser utilizado.
  Assim, tem cuidadosamente rever o resultado destes comandos, examinar o conteúdo da tabela sysxlogins e resolver estes erros em conformidade.
• O valor de SID para um início de sessão específico é utilizado como base para implementar o acesso ao nível da base de dados no SQL Server. Por conseguinte, se o mesmo início de sessão tem dois valores diferentes para o SID no nível da base de dados (em duas bases de diferentes dados nesse servidor), o início de sessão só terá acesso à base de dados cujo SID corresponda ao valor syslogins para esse início de sessão. Tal situação pode ocorrer se a bases de dois dados em questão tiverem sido consolidados de dois servidores diferentes. Para resolver este problema, o login será têm de ser manualmente removidas da base de dados que tem um erro de correspondência de SID utilizando o procedimento armazenado sp_dropuser e adicionado novamente utilizando o procedimento sp_adduser armazenados.

Voltar ao topo | Submeter comentários