Transferir logons e senhas entre instâncias do SQL Server

Versão original do produto: SQL Server
Número original do KB: 918992, 246133

Resumo

Este artigo mostra como transferir SQL Server logons e senhas entre instâncias de Microsoft SQL Server em execução no Windows. Use estes procedimentos durante cenários de migração, restauração ou alta disponibilidade do SQL Server para manter intacta a autenticação dos usuários e evitar usuários órfãos no banco de dados da instância de destino. As instâncias de origem e de destino podem estar no mesmo servidor ou em servidores diferentes, e suas versões podem ser diferentes.

Por que transferir logons entre instâncias de SQL Server

Quando você move um banco de dados para um novo servidor, por exemplo, durante uma migração ou restauração, os usuários do banco de dados se movem com o banco de dados, mas os logons correspondentes no nível do servidor podem não existir na nova instância. Essa incompatibilidade produz usuários órfãos. A transferência de logons e senhas mantém a autenticação do usuário intacta e impede interrupções de entrada após a movimentação de um banco de dados.

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

Falha no logon do usuário "MyUser" (Microsoft SQL Server, Erro: 18456)

Esse problema ocorre porque os logons da instância do SQL Server no servidor A não existem na instância do SQL Server no servidor B.

O erro 18456 também pode ocorrer por vários outros motivos. Para obter mais informações sobre as várias causas e suas resoluções, consulte MSSQLSERVER_18456.

Métodos para transferir logons entre instâncias de SQL Server

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

Gerar scripts de logon no SSMS e redefinir senhas no destino

Você pode gerar scripts de logon no SQL Server Management Studio (SSMS) usando a opção Gerar Scripts para um banco de dados.

Para gerar scripts por meio do SSMS no servidor de origem e redefinir manualmente as senhas para logons do SQL Server no servidor de destino, siga estas etapas:

  1. Conecte-se ao servidor A que está hospedando o SQL Server de origem.

  2. Expanda o nó Databases.

  3. Selecione e segure (ou clique com o botão direito do mouse) em qualquer banco de dados do usuário e selecione Tarefas>Gerar Scripts.

  4. A página Introdução é aberta. Selecione Avançar para abrir a página Escolher Objetos . Selecione Criar script de todo o banco de dados e de todos os objetos do banco de dados.

  5. Selecione Avançar para abrir a página Definir Opções de Script.

  6. Selecione o botão Avançado para opções de logon de script.

  7. Na lista Avançado, localize Logons de Script, defina a opção como Verdadeiro e selecione OK.

  8. Retorne para Definir Opções de Script, em Selecionar como os scripts devem ser salvos , selecione Abrir na nova janela de consulta.

  9. Selecione Avançar duas vezes e, em seguida, selecione Concluir.

  10. Localize a seção no script que contém logins. Normalmente, o script gerado contém texto com o seguinte comentário no início desta seção:

    /* For security reasons the login is created disabled and with a random password. */

    Observação

    Este comentário indica que os logons de Autenticação do SQL Server são gerados com uma senha aleatória e são desabilitados por padrão. Você deve redefinir a senha e reabilitar esses acessos no servidor de destino.

  11. Aplique o script de login do script gerado completo ao SQL Server de destino.

  12. Para qualquer logon de Autenticação do SQL Server, redefina a senha no SQL Server de destino e habilite novamente esses logons.

Transferir logons e senhas usando sp_help_revlogin

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

    USE [master]
GO
IF OBJECT_ID('dbo.sp_hexadecimal') IS NOT NULL
    DROP PROCEDURE dbo.sp_hexadecimal
GO
CREATE PROCEDURE dbo.sp_hexadecimal
    @binvalue [varbinary](256)
    ,@hexvalue [nvarchar] (514) OUTPUT
AS
BEGIN
    DECLARE @i [smallint]
    DECLARE @length [smallint]
    DECLARE @hexstring [nchar](16)
    SELECT @hexvalue = N'0x'
    SELECT @i = 1
    SELECT @length = DATALENGTH(@binvalue)
    SELECT @hexstring = N'0123456789ABCDEF'
    WHILE (@i < =  @length)
    BEGIN
        DECLARE @tempint   [smallint]
        DECLARE @firstint  [smallint]
        DECLARE @secondint [smallint]
        SELECT @tempint = CONVERT([smallint], SUBSTRING(@binvalue, @i, 1))
        SELECT @firstint = FLOOR(@tempint / 16)
        SELECT @secondint = @tempint - (@firstint * 16)
        SELECT @hexvalue = @hexvalue
            + SUBSTRING(@hexstring, @firstint  + 1, 1)
            + SUBSTRING(@hexstring, @secondint + 1, 1)
        SELECT @i = @i + 1
    END
END
GO
IF OBJECT_ID('dbo.sp_help_revlogin') IS NOT NULL
    DROP PROCEDURE dbo.sp_help_revlogin
GO
CREATE PROCEDURE dbo.sp_help_revlogin
    @login_name [sysname] = NULL
AS
BEGIN
    DECLARE @name                  [sysname]
    DECLARE @type                  [nvarchar](1)
    DECLARE @hasaccess             [int]
    DECLARE @denylogin             [int]
    DECLARE @is_disabled           [int]
    DECLARE @PWD_varbinary         [varbinary](256)
    DECLARE @PWD_string            [nvarchar](514)
    DECLARE @SID_varbinary         [varbinary](85)
    DECLARE @SID_string            [nvarchar](514)
    DECLARE @tmpstr                [nvarchar](4000)
    DECLARE @is_policy_checked     [nvarchar](3)
    DECLARE @is_expiration_checked [nvarchar](3)
    DECLARE @Prefix                [nvarchar](4000)
    DECLARE @defaultdb             [sysname]
    DECLARE @defaultlanguage       [sysname]
    DECLARE @tmpstrRole            [nvarchar](4000)
    IF @login_name IS NULL
    BEGIN
        DECLARE login_curs CURSOR
        FOR
        SELECT p.[sid],p.[name],p.[type],p.is_disabled,p.default_database_name,l.hasaccess,l.denylogin,default_language_name = ISNULL(p.default_language_name,@@LANGUAGE)
        FROM sys.server_principals p
        LEFT JOIN sys.syslogins l ON l.[name] = p.[name]
        WHERE p.[type] IN ('S' /* SQL_LOGIN */,'G' /* WINDOWS_GROUP */,'U' /* WINDOWS_LOGIN */)
            AND p.[name] <> 'sa'
            AND p.[name] not like '##%'
        ORDER BY p.[name]
    END
    ELSE
        DECLARE login_curs CURSOR
        FOR
        SELECT p.[sid],p.[name],p.[type],p.is_disabled,p.default_database_name,l.hasaccess,l.denylogin,default_language_name = ISNULL(p.default_language_name,@@LANGUAGE)
        FROM sys.server_principals p
        LEFT JOIN sys.syslogins l ON l.[name] = p.[name]
        WHERE p.[type] IN ('S' /* SQL_LOGIN */,'G' /* WINDOWS_GROUP */,'U' /* WINDOWS_LOGIN */)
            AND p.[name] <> 'sa'
            AND p.[name] NOT LIKE '##%'
            AND p.[name] = @login_name
        ORDER BY p.[name]
    OPEN login_curs
    FETCH NEXT FROM login_curs INTO @SID_varbinary,@name,@type,@is_disabled,@defaultdb,@hasaccess,@denylogin,@defaultlanguage
    IF (@@fetch_status = - 1)
    BEGIN
        PRINT '/* No login(s) found for ' + QUOTENAME(@login_name) + N'. */'
        CLOSE login_curs
        DEALLOCATE login_curs
        RETURN - 1
    END
    SET @tmpstr = N'/* sp_help_revlogin script
** Generated ' + CONVERT([nvarchar], GETDATE()) + N' on ' + @@SERVERNAME + N'
*/'
    PRINT @tmpstr
    WHILE (@@fetch_status <> - 1)
    BEGIN
        IF (@@fetch_status <> - 2)
        BEGIN
            PRINT ''
            SET @tmpstr = N'/* Login ' + QUOTENAME(@name) + N' */'
            PRINT @tmpstr
            SET @tmpstr = N'IF NOT EXISTS (
    SELECT 1
    FROM sys.server_principals
    WHERE [name] = N''' + @name + N'''
    )
BEGIN'
            PRINT @tmpstr
            IF @type IN ('G','U') -- NT-authenticated Group/User
            BEGIN -- NT authenticated account/group 
                SET @tmpstr = N'    CREATE LOGIN ' + QUOTENAME(@name) + N'
    FROM WINDOWS
    WITH DEFAULT_DATABASE = ' + QUOTENAME(@defaultdb) + N'
        ,DEFAULT_LANGUAGE = ' + QUOTENAME(@defaultlanguage)
            END
            ELSE
            BEGIN -- SQL Server authentication
                -- obtain password and sid
                SET @PWD_varbinary = CAST(LOGINPROPERTY(@name, 'PasswordHash') AS [varbinary](256))
                EXEC dbo.sp_hexadecimal @PWD_varbinary, @PWD_string OUT
                EXEC dbo.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 = NCHAR(9) + N'CREATE LOGIN ' + QUOTENAME(@name) + N'
    WITH PASSWORD = ' + @PWD_string + N' HASHED
        ,SID = ' + @SID_string + N'
        ,DEFAULT_DATABASE = ' + QUOTENAME(@defaultdb) + N'
        ,DEFAULT_LANGUAGE = ' + QUOTENAME(@defaultlanguage)

                IF @is_policy_checked IS NOT NULL
                BEGIN
                    SET @tmpstr = @tmpstr + N'
        ,CHECK_POLICY = ' + @is_policy_checked
                END

                IF @is_expiration_checked IS NOT NULL
                BEGIN
                    SET @tmpstr = @tmpstr + N'
        ,CHECK_EXPIRATION = ' + @is_expiration_checked
                END
            END
            IF (@denylogin = 1)
            BEGIN -- login is denied access
                SET @tmpstr = @tmpstr
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'DENY CONNECT SQL TO ' + QUOTENAME(@name)
            END
            ELSE IF (@hasaccess = 0)
            BEGIN -- login exists but does not have access
                SET @tmpstr = @tmpstr
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'REVOKE CONNECT SQL TO ' + QUOTENAME(@name)
            END
            IF (@is_disabled = 1)
            BEGIN -- login is disabled
                SET @tmpstr = @tmpstr
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'ALTER LOGIN ' + QUOTENAME(@name) + N' DISABLE'
            END
            SET @Prefix =
                NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'EXEC [master].dbo.sp_addsrvrolemember @loginame = N'''
            SET @tmpstrRole = N''
            SELECT @tmpstrRole = @tmpstrRole
                + CASE WHEN sysadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''sysadmin''' ELSE '' END
                + CASE WHEN securityadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''securityadmin''' ELSE '' END
                + CASE WHEN serveradmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''serveradmin''' ELSE '' END
                + CASE WHEN setupadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''setupadmin''' ELSE '' END
                + CASE WHEN processadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''processadmin''' ELSE '' END
                + CASE WHEN diskadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''diskadmin''' ELSE '' END
                + CASE WHEN dbcreator = 1 THEN @Prefix + LoginName + N''', @rolename = N''dbcreator''' ELSE '' END
                + CASE WHEN bulkadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''bulkadmin''' ELSE '' END
            FROM (
                SELECT
                    SUSER_SNAME([sid])AS LoginName
                    ,sysadmin
                    ,securityadmin
                    ,serveradmin
                    ,setupadmin
                    ,processadmin
                    ,diskadmin
                    ,dbcreator
                    ,bulkadmin
                FROM sys.syslogins
                WHERE (    sysadmin <> 0
                        OR securityadmin <> 0
                        OR serveradmin <> 0
                        OR setupadmin <> 0
                        OR processadmin <> 0
                        OR diskadmin <> 0
                        OR dbcreator <> 0
                        OR bulkadmin <> 0
                        )
                    AND [name] = @name
                ) L
            IF @tmpstr <> '' PRINT @tmpstr
            IF @tmpstrRole <> '' PRINT @tmpstrRole
            PRINT 'END'
        END
        FETCH NEXT FROM login_curs INTO @SID_varbinary,@name,@type,@is_disabled,@defaultdb,@hasaccess,@denylogin,@defaultlanguage
    END
    CLOSE login_curs
    DEALLOCATE login_curs
    RETURN 0
END

    Observação

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

  2. No editor de consultas do SSMS, selecione a opção Resultados para Texto .

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

    EXEC sp_help_revlogin

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

  5. Examine e siga as informações na seção Considerações adicionais ao transferir a seção de logons do SQL Server antes de prosseguir com as etapas de implementação no servidor de destino.

  6. Depois de concluir todas as etapas aplicáveis da seção Considerações adicionais ao transferir logons do SQL Server, conecte-se ao servidor de destino B usando qualquer ferramenta cliente (como o SSMS).

  7. Execute o script gerado como saída de sp_helprevlogin do servidor A.

Considerações adicionais ao transferir logons do SQL Server

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

Entender o hash de senha nas transferências de logon do SQL Server

O SQL Server faz hash das senhas das seguintes maneiras:

  • VERSION_SHA1: usa o algoritmo SHA1. Do SQL Server 2000 ao SQL Server 2008 R2 usam esse tipo de hash. Essas versões estão fora do suporte principal e estendido, portanto, você só deverá encontrar hashes VERSION_SHA1 ao deixar de usar instâncias legadas.
  • VERSION_SHA2: usa o algoritmo SHA2-512. SQL Server 2012 e posteriores, incluindo as versões atualmente com suporte, usam esse hash.

O script de saída cria os logons usando a senha criptografada. O HASHED argumento na instrução CREATE LOGIN causa esse comportamento. Esse argumento indica que a senha inserida após o argumento PASSWORD já está hashada.

Manipular alterações de domínio durante transferências de logon do SQL Server

Se os servidores de origem e destino estiverem em domínios diferentes, examine cuidadosamente o script de saída. Altere o script para substituir o nome de domínio original pelo novo nome de domínio nas CREATE LOGIN instruções. Os logins integrados aos quais foi concedido acesso no novo domínio não têm o mesmo SID dos logins no domínio original, de modo que os usuários ficam órfãos em relação a esses logins. Para corrigir usuários órfãos, consulte Solucionar problemas de usuários órfãos (SQL Server) e ALTER USER.

Se o servidor A e o servidor B estão no mesmo domínio, o mesmo SID é usado. Portanto, os usuários não são órfãos.

Permissões necessárias para exibir e selecionar logons do SQL Server

Por padrão, somente membros da função de servidor fixa sysadmin podem executar uma instrução SELECT na exibição sys.server_principals. A menos que um sysadmin conceda as permissões necessárias a outros usuários, esses usuários não poderão criar ou executar o script de saída.

A configuração padrão do banco de dados não é incluída no script nem transferida

As etapas neste artigo não transferem as informações do banco de dados padrão para um logon específico. Essa limitação existe porque o banco de dados padrão pode nem sempre existir no servidor B. Para definir o banco de dados padrão para um logon, use a instrução ALTER LOGIN passando o nome de logon e o banco de dados padrão como argumentos.

Gerenciar diferenças de ordem de classificação em transferências de logon do SQL Server

Os servidores de origem e de destino podem ter ordens de classificação diferentes ou podem usar a mesma ordem de classificação. Veja como você pode abordar cada cenário:

  • Servidor insensível a maiúsculas e minúsculas A e servidor sensível a maiúsculas e minúsculas B: a ordem de classificação do servidor A é insensível a maiúsculas e minúsculas, enquanto a do servidor B é sensível. Nesse caso, os usuários devem digitar as senhas em letras maiúsculas depois que você transferir os logins e as senhas para a instância no servidor B.

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

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

  • Diferencia maiúsculas de minúsculas ou não diferencia maiúsculas de minúsculas em ambos os servidores: a ordem de classificação tanto do servidor A quanto do servidor B diferencia maiúsculas de minúsculas, ou a ordem de classificação tanto do servidor A quanto do servidor B não diferencia maiúsculas de minúsculas. Nesses casos, os usuários não enfrentam problemas.

Corrigir conflitos com logons existentes no servidor de destino

O script verifica se o logon existe no servidor de destino e cria um logon somente se ele não existe. No entanto, se você receber a seguinte mensagem de erro ao executar o script de saída na instância no servidor B, deverá resolver manualmente o conflito seguindo as etapas desta seção.

Mensagem 15025, Nível 16, Estado 1, Linha 1
A entidade de segurança do servidor 'MyLogin' já existe.

Da mesma forma, um login que já existe na instância do servidor B pode ter um SID igual ao de um SID no script gerado. Nesse caso, você receberá a seguinte mensagem de erro ao executar o script de saída na instância no servidor B:

Msg 15433, Nível 16, Estado 1, Linha 1 O parâmetro sid fornecido está em uso.

Para corrigir o conflito manualmente, siga estas etapas:

  1. Examine o script de saída com cuidado.
  2. Examine o conteúdo da exibição sys.server_principals na instância do servidor B.
  3. Execute a ação apropriada para cada erro, como remover ou renomear o logon conflitante no servidor B ou remover a instrução duplicada CREATE LOGIN do script de saída antes de executar novamente.

Em SQL Server, o SID para um logon rege o acesso no nível do banco de dados. Um logon pode ter SIDs diferentes quando mapeado para usuários em bancos de dados diferentes, o que pode acontecer se você combinar manualmente bancos de dados de diferentes servidores. Nesse caso, o logon só pode usar o banco de dados em que o SID da entidade de banco de dados corresponde ao SID na exibição sys.server_principals . Para corrigir esse problema, exclua o usuário do banco de dados que tem o SID incompatível usando a instrução DROP USER. Em seguida, adicione o usuário novamente com a instrução CREATE USER e mapeie-o para o login correto (principal de servidor).

Para obter mais informações sobre entidades de servidor e banco de dados, consulte CREATE USER e CREATE LOGIN.

Cenários avançados e solução de problemas

Se você continuar a ver falhas de entrada depois de transferir logons, verifique os seguintes itens:

Conteúdo relacionado

  • Last updated on