Ir para o conteúdo principal

[SPIKE][BAYMAX] - Migração de usuários e senhas de banco de dados SQL Server

Transferir logons e senhas entre instâinstâncias do SQL Server

Este artigo descreve como transferir os logons e as senhas entre diferentes instâinstâncias do SQL Server em execuçãexecução no Windows.

VersãVersão do produto original:   SQL Server

NúNúmero do KB original:   918992

IntroduçãIntrodução

Este artigo descreve como transferir os logons e senhas entre diferentes instâinstâncias do Microsoft SQL Server.

 ObservaçãObservação

As instâinstâncias podem estar no mesmo servidor ou em servidores diferentes e suas versõversões podem ser diferentes.

Mais informaçõinformações

Neste artigo, o servidor A e o servidor B sãsão servidores diferentes.

Depois de mover um banco de dados da instâinstância do SQL Server no servidor A para a instâinstância do SQL Server no servidor B, os usuáusuários podem nãnão conseguir fazer logon no banco de dados no servidor B. AléAlém disso, os usuáusuários podem receber a seguinte mensagem de erro:

Falha de logon para o usuáusuário 'MyUser'. (Microsoft SQL Server, erro: 18456)

Esse problema ocorre porque vocêvocê nãnão transferiu os logons e as senhas da instâinstância do SQL Server no servidor a para a instâinstância do SQL Server no servidor B.

 ObservaçãObservação

A mensagem de erro 18456 tambétambém ocorre devido a outros motivos. Para obter informaçõinformações adicionais sobre essas causas e possípossíveis revisõrevisões de resoluçõresoluções MSSQLSERVER_18456.

Para transferir os logons, use um dos seguintes mémétodos, conforme apropriado para a sua situaçãsituação.

  • MéMétodo 1: redefinir a senha no computador SQL Server de destino (servidor B)

    Para resolver esse problema, redefina a senha no computador do SQL Server e façfaça o script do logon.

     ObservaçãObservação

    O algoritmo de hash de senha éé usado quando vocêvocê redefine a senha.

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

    1. Criar procedimentos armazenados que irãirão ajudar a gerar scripts necessánecessários para transferir logons e suas senhas. Para fazer isso, conecte-se ao servidor A usando o SQL Server Management Studio (SSMS) ou qualquer outra ferramenta de cliente e execute o seguinte script:

      SQL 
      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çãObservação

      Esse script cria dois procedimentos armazenados no banco de dados mestre. Os procedimentos sãsão nomeados sp_hexadecimal e sp_help_revlogin .

    2. No editor de consulta SSMS, selecione a opçãopção resultados para texto .

    3. Execute a seguinte instruçãinstrução no mesmo ou em uma nova janela de consulta:

      SQL 
      EXEC sp_help_revlogin

    4. O script de saísaída sp_help_revlogin gerado pelo procedimento armazenado éé o script de logon. Esse script de logon cria os logons que têtêm o identificador de segurançsegurança (SID) original e a senha original.

 Importante

Revise as informaçõinformações na seçãseção comentácomentários abaixo antes de prosseguir com a implementaçãimplementação de etapas no servidor de destino.

Etapas no servidor de destino (servidor B)

Conecte-se ao servidor B usando qualquer ferramenta de cliente (como SSMS) e execute o script gerado na etapa 4 (saísaída de sp_helprevlogin ) do servidor A.

ComentáComentários

Revise as seguintes informaçõinformações antes de executar o script de saísaída na instâinstância no servidor B:

  • Uma senha pode ser codificada das seguintes maneiras:

    • VERSION_SHA1: Esse hash éé gerado usando o 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õversões posteriores.

  • Revise o script de saísaída cuidadosamente. Se o servidor A e o servidor B estiverem em domídomínios diferentes, vocêvocê precisaráprecisará alterar o script de saísaída. Em seguida, vocêvocê precisa substituir o nome de domídomínio original usando o novo nome de domídomínio nas instruçõinstruções CREATE LOGIN. Os logons integrados que recebem acesso no novo domídomínio nãnão têtêm o mesmo SID que os logons no domídomínio original. Portanto, os usuáusuários ficam órfãórfãos desses logons. Para obter mais informaçõinformações sobre como resolver esses usuáusuários órfãórfãos, consulte como resolver problemas de permissãpermissão ao mover um banco de dados entre servidores que estãestão executando o SQL Server.

    Se o servidor A e o servidor B estiverem no mesmo domídomínio, o mesmo SID seráserá usado. Portanto, éé improváimprovável que os usuáusuários fiquem órfãórfãos.

  • No script de saísaída, os logons sãsão criados usando a senha criptografada. Isso se deve ao argumento HASH na CREATE LOGIN instruçãinstrução. Este argumento especifica que a senha inserida apóapós o argumento PASSWORD jájá éé hash.

  • Por padrãpadrão, apenas um membro da funçãfunção de servidor fixa sysadmin pode executar uma SELECT instruçãinstrução do sys.server_principals modo de exibiçãexibição. A menos que um membro da funçãfunção de servidor fixa sysadmin conceda as permissõpermissões necessánecessárias aos usuáusuários, os usuáusuários nãnão podem criar ou executar o script de saísaída.

  • As etapas neste artigo nãnão transferem as informaçõinformações de banco de dados padrãpadrão para um determinado login. Isso ocorre porque o banco de dados padrãpadrão pode nãnão existir sempre no servidor B. Para definir o banco de dados padrãpadrão para um logon, use a ALTER LOGIN instruçãinstrução passando o nome de logon e o banco de dados padrãpadrão como argumentos.

  • Ordenar pedidos nos servidores de origem e de destino:

    • Servidor que nãnão diferencia maiúmaiúsculas de minúminúsculas e servidor B: a ordem de classificaçãclassificação do servidor a pode diferenciar maiúmaiúsculas de minúminúsculas, e a ordem de classificaçãclassificação do servidor B pode diferenciar maiúmaiúsculas de minúminúsculas. Nesse caso, os usuáusuários devem digitar as senhas em todas as letras maiúmaiúsculas depois que vocêvocê transferir os logons e as senhas para a instâinstância no servidor B.

    • Servidor que diferencia maiúmaiúsculas de minúminúsculas e servidor B: A ordem de classificaçãclassificação do servidor A pode diferenciar maiúmaiúsculas de minúminúsculas, e a ordem de classificaçãclassificação do servidor B pode diferenciar maiúmaiúsculas de minúminúsculas. Nesse caso, os usuáusuários nãnão podem fazer logon usando os logons e as senhas que vocêvocê transfere para a instâinstância no servidor B, a menos que uma das seguintes condiçõcondições seja verdadeira:

      • As senhas originais nãnão contêcontêm letras.
      • Todas as letras nas senhas originais estãestão em letras maiúmaiúsculas.

    • Diferenciar maiúmaiúsculas de minúminúsculas ou diferenciar maiúmaiúsculas de minúminúsculas em ambos os servidores: a ordem de classificaçãclassificação do servidor a e do servidor b pode diferenciar maiúmaiúsculas de minúminúsculas, ou a ordem de classificaçãclassificação do servidor a e do servidor b pode diferenciar maiúmaiúsculas de minúminúsculas. Nesses casos, os usuáusuários nãnão enfrentam um problema.

  • Um logon que jájá estáestá na instâinstância no servidor B pode ter um nome igual ao nome do script de saísaída. Nesse caso, vocêvocê receberáreceberá a seguinte mensagem de erro quando executar o script de saísaída na instâinstância no servidor B:

    MSG 15025, nínível 16, estado 1, linha 1

    A entidade de segurançsegurança do servidor 'MyLogin' jájá existe.

    Da mesma forma, um logon que jájá estáestá na instâinstância no servidor B pode ter um SID que éé o mesmo que um SID no script de saísaída. Nesse caso, vocêvocê receberáreceberá a seguinte mensagem de erro quando executar o script de saísaída na instâinstância no servidor B:

    MSG 15433, nínível 16, estado 1, linha 1 o parâparâmetro fornecido por Sid estáestá em uso.

    Portanto, vocêvocê deve fazer o seguinte:

    1. Revise o script de saísaída cuidadosamente.

    2. Examine o conteúconteúdo do modo de exibiçãexibição sys.server_principals na instâinstância no servidor B.

    3. Solucionar essas mensagens de erro conforme apropriado.

      No SQL Server 2005, o SID de um logon éé usado para implementar o acesso no nínível do banco de dados. Um logon pode ter diferentes SIDs em diferentes bancos de dados em um servidor. Nesse caso, o logon sósó pode acessar o banco de dados que tem o SID que corresponde ao SID no sys.server_principals modo de exibiçãexibição. Esse problema pode ocorrer se os dois bancos de dados forem combinados de servidores diferentes. Para resolver esse problema, remova manualmente o logon do banco de dados que tem uma incompatibilidade de SID usando a instruçãinstrução DROP USER. Em seguida, adicione o logon novamente usando a CREATE USER instruçãinstrução.

ReferêReferências

Voltar ao topo