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

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

Como transferir os inícios de sessão e palavras-passe de SQL Server 7.0 para o SQL Server 2000 ou entre servidores que estão a executar 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, Os volumes locaiscom o botão direito e, em seguida, clique em Novo pacote.
2. Depois dos DTS compactar designer abre, clique em Tarefa 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 adequado.
   
   Importante O servidor de destino do SQL Server 2000 não pode estar em execução 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 estiver a importar os inícios de sessão de uma instância do SQL Server que se encontra num computador separado, será a instância do SQL Server tem de ter uma conta de domínio para concluir a tarefa.
   
   Nota O método DTS transferirá as palavras-passe mas não o original SID. Se não for criado um início de sessão utilizando o SID e o utilizador original bases de dados forem também transferidas para um novo servidor, os utilizadores da base de dados isolado a partir do início de sessão. Para transferir o SID original e ignorar o isolado os utilizadores, siga os passos na secção "Resolução concluída a transferência 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 dos métodos seguintes criar dois procedimentos armazenados com o nome do procedimento armazenado sp_hexadecimal e o procedimento de sp_help_revlogin armazenados na base de dados principal .
• Os scripts estão dependentes de tabelas de sistema do SQL Server. Pode alterar a estrutura destas tabelas entre versões do SQL Server. Não é aconselhada a seleccionar directamente a partir de tabelas de sistema.
• Rever os comentários no fim deste artigo para importantes informações sobre os passos nos métodos.
• Método 2 atribui 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 o inícios de sessão e palavras-passe entre servidores que estão a executar o SQL Server 2000.

1. Execute o seguinte script na origem do SQL Server. Continuar para o passo 2, quando terminar de criar o sp_help_revlogin procedimento armazenado.

   ----- 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 armazenado sp_help_revlogin , execute o procedimento de 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 está scripts de início de sessão que criar inícios de sessão com o SID e palavra-passe originais. Guardar a saída e, em seguida, colar e executá-la numa consulta Analisador de de 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 SQL Server 2005.
• Atribuir os inícios de sessão 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 a saída e, em seguida, colar e executar a saída no SQL Server Management Studio no destino de SQL Server 2005.

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

Observações

• 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 de SQL Editar servidor num domínio diferente do que a instância de origem do SQL Server, o script gerado pelo procedimento de sp_help_revlogin e substitua o nome de domínio com o novo domínio a demonstrações de sp_grantlogin . Uma vez que os inícios de sessão integrados concedido acesso do novo domínio não terão os mesmos SID como os inícios de sessão no domínio original, o os utilizadores da base de dados vão ser isolados destes inícios de sessão. Para resolver estes isolado os utilizadores, consulte os artigos referenciados no item com marcas seguinte. Se transferir os inícios de sessão integrados entre instâncias do SQL Server no mesmo domínio, os mesmos SID é utilizado e o utilizador não é susceptível de 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 um "utilizador órfão". Se tentar conceder o acesso de início de sessão na base de dados, pode falha indicando que o utilizador já existe:
  Microsoft SQL-DMO (ODBC SQLState: 42000) 15023 de erro: utilizador ou função '% s' já existe base de dados actual.
  Para obter instruções sobre como mapear os inícios de sessão os utilizadores da base de dados para resolver órfãos inícios de sessão do SQL Server e inícios de sessão integrados, consulte o seguinte artigo na Microsoft Knowledge Base:
  240872

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

  Como resolver problemas de permissões quando move uma base de dados entre servidores que estão a executar o SQL Server
  Para obter instruções sobre como utilizar o sp_change_users_login de procedimento para corrigir os utilizadores órfãos um a um armazenado (isto apenas tratará os utilizadores, abandonados de inícios de sessão SQL padrão), consulte o seguinte artigo na Microsoft Knowledge Base:
  274188

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

  "Utilizadores de Orphaned de resolução de problemas" tópico no Books Online está incompleto
• Se a transferência de inícios de sessão e palavras-passe fizer parte de uma operação mover bases de dados para um novo servidor com o SQL Server, consulte o seguinte artigo no Dados de conhecimento para obter uma descrição do fluxo de trabalho e passos causa:
  314546

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

  Como mover bases de dados entre computadores com o SQL Server
• Pode fazê-lo devido ao parâmetro @encryptopt no procedimento armazenado do sistema sp_addlogin , que permite um início de sessão criado por utilizar 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, apenas os membros da função de servidor sysadminfixed Pode seleccionar a partir da tabela sysxlogins . A menos que um membro da função sysadmin concede as necessárias permissões, os utilizadores finais não é possível criar ou executar estes procedimentos armazenados.
• Esta abordagem não tenta transferir base de dados predefinida informações para um início de sessão especial porque a base de dados predefinida pode nem sempre existe no servidor de destino. Para definir a base de dados predefinida para um login Pode utilizar o procedimento armazenado do sistema de sp_defaultdb , passando-lhe o nome de início de sessão e o base de dados predefinida como argumentos. Para mais informações sobre como utilizar este procedimento, consulte o tópico "sp_defaultdb" no SQL Server Books Online.
• Durante uma transferência de inícios de sessão entre instâncias de SQL Servidor, se a ordem pela qual o servidor de origem for maiúsculas e minúsculas e a ordenação ordem de servidor de destino é entre maiúsculas e minúsculas, tem de introduzir todos 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 estiver a ordem pela qual o servidor de origem diferenciar maiúsculas de minúsculas e é a ordem pela qual o servidor de destino maiúsculas e minúsculas, não será capaz de iniciar sessão com a logins transferida utilizando o procedimento descrito neste artigo, a menos que a palavra-passe original contém caracteres não alfabéticos ou a menos que todos os caracteres alfabéticos no a palavra-passe original são caracteres em maiúsculas. Se ambos os servidores forem diferenciar maiúsculas de minúsculas ou ambos os servidores são maiúsculas e minúsculas, não detectará Este problema. Este é um efeito de lado da forma como a que processa o SQL Server palavras-passe. Para mais informações, consulte "efeito de palavras-passe de alterar ordenação Tópico de encomendas"no SQL Server 7.0 Books Online.
• Quando executa a saída do sp_help_revlogin script no servidor de destino, se o servidor de destino já tiver definido um início de sessão com o mesmo nome que um dos inícios de sessão no resultado do script, poderá ver a a seguinte mensagem de erro quando a execução da saída do sp_help_revlogin script:
  Servidor: Erro 15025, Nível de 16 de 1 de estado, procedimento sp_addlogin, 56 de linha
  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 como o que está a tentar adicionar, receber a seguinte mensagem de erro:
  Servidor: Msg 15433, De 16 de nível, de 1 de estado, procedimento sp_addlogin, linha 93
  Fornecido o parâmetro @sid está em utilização.
  Por conseguinte, é necessário cuidadosamente Reveja a saída 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 a nível da base de dados (em dois bases de dados diferentes nesse servidor), o início de sessão só terão acesso a que a base de dados cujo SID corresponda ao valor syslogins para esse início de sessão. Esta situação poderá ocorrer se as duas bases de dados em questão tenham sido consolidados de dois servidores diferentes. Para resolver este problema problema, o início de sessão em questão teria de ser removida manualmente das base de dados com um erro de correspondência de SID, utilizando o sp_dropuser procedimento armazenado e, em seguida, adicionado novamente utilizando o procedimento armazenado sp_adduser .

Voltar ao topo | Submeter comentários