Transferir logons e senhas entre instâncias do SQL Server

Artigo
01/17/2025

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

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

Introdução

Este artigo descreve como transferir os logons e senhas entre diferentes instâncias do Microsoft SQL Server. As instâncias podem estar no mesmo servidor ou em servidores diferentes, e suas versões podem ser diferentes.

Por que transferir logons entre instâncias do SQL Server?

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

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 fazer logon 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.

Lembre-se de que o erro 18456 ocorre por muitos outros motivos. Para obter mais informações sobre essas causas e suas resoluções, consulte MSSQLSERVER_18456.

Etapas para transferir os logins

Para transferir os logins, use um dos seguintes métodos, conforme apropriado à situação.

Método 1: gerar scripts por meio do SSMS no servidor de origem e redefinir manualmente as senhas para logons do SQL Server no servidor de 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:

Conecte-se ao servidor A que está hospedando o SQL Server de origem.
Expanda o nó Bancos de Dados .
Clique com o botão direito do mouse em qualquer banco de dados de usuário e selecione Tarefas>Gerar Scripts.
A página Introdução é aberta. Selecione Avançar para abrir a página Escolher Objetos . Selecione Gerar script de todo o banco de dados e todos os objetos de banco de dados.
Selecione Avançar para abrir a página Definir Opções de Script.
Selecione o botão Avançado para opções de login de script.
Na lista Avançado, localize Logons de Script, defina a opção como Verdadeiro e selecione OK.
Voltar para Definir opções de script em Selecione como os scripts devem ser salvos e selecione Abrir em nova janela de consulta.
Selecione Avançar duas vezes e, em seguida, selecione Concluir.
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

Isso indica que os logons de Autenticação do SQL Server são gerados com uma senha aleatória e estão desabilitados por padrão. Você precisa redefinir a senha e reativar esses logins no servidor de destino.
Aplique o script de logon do script gerado maior ao SQL Server de destino.
Para qualquer logon de Autenticação do SQL Server, redefina a senha no SQL Server de destino e habilite novamente esses logons.

Método 2: Transferir logins e senhas para o servidor de destino (Servidor B) usando scripts gerados no servidor de origem (Servidor A)

Crie procedimentos armazenados que ajudarão 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 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.

No editor de consultas do SSMS, selecione a opção Resultados em Texto.
Execute a seguinte instrução na mesma janela ou em uma nova janela de consulta:
```
EXEC sp_help_revlogin
```
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.
Revise e siga as informações na seção Comentários antes de prosseguir com a implementação de etapas no servidor de destino.
Depois de implementar as etapas aplicáveis da seção Comentários , conecte-se ao servidor de destino B usando qualquer ferramenta cliente (como SSMS).
Execute o script gerado como saída do sp_helprevlogin servidor A.

Comentários

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

Informações de hash de senha

Uma senha pode ter hash das seguintes maneiras:
- VERSION_SHA1: esse hash é gerado usando o algoritmo SHA1 e é usado no SQL Server 2000 ao SQL Server 2008 R2.
- VERSION_SHA2: esse hash é gerado usando o algoritmo SHA2 512 e é usado no SQL Server 2012 e versões posteriores.
No script de saída, os logons são criados usando a senha criptografada. Isso ocorre por causa do HASHED argumento na CREATE LOGIN declaração. Esse argumento especifica que a senha inserida após o PASSWORD argumento já está com hash.

Como lidar com a mudança de domínios

Seus servidores de origem e destino estão em domínios diferentes? Examine o script de saída com cuidado. Se o servidor A e o servidor B estão em domínios diferentes, você precisa alterar o script de saída. Em seguida, você deve substituir o nome de domínio original usando o CREATE LOGIN novo nome de domínio nas instruções. Os logons integrados que recebem acesso no novo domínio não têm o mesmo SID que os logons no domínio original. Portanto, os usuários são órfãos desses logons. Para obter mais informações sobre como resolver esses 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, é improvável que os usuários sejam órfãos.

Permissões para visualizar e selecionar todos os logins

Por padrão, somente um membro da função de servidor fixo sysadmin pode executar uma instrução SELECT da exibição sys.server_principals. A menos que um membro da função de servidor fixa sysadmin conceda as permissões necessárias aos usuários, os 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 é roteirizada e transferida

As etapas neste artigo não transferem as informações do banco de dados padrão para um logon específico. Isso ocorre 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 ALTER LOGIN instrução passando o nome de logon e o banco de dados padrão como argumentos.

Como lidar com diferentes ordens de classificação entre os servidores de origem e de destino

Pode haver diferenças nas ordens de classificação entre os servidores de origem e de destino, ou eles podem ser os mesmos. Veja como cada cenário pode ser abordado:

Servidor A que não diferencia maiúsculas de minúsculas e servidor B que diferencia maiúsculas de minúsculas: a ordem de classificação do servidor A pode não diferenciar maiúsculas de minúsculas e a ordem de classificação do servidor B pode diferenciar maiúsculas de minúsculas. Nesse caso, os usuários devem digitar as senhas em todas as letras maiúsculas depois que você transferir os logons e as senhas para a instância no servidor B.
Servidor A que diferencia maiúsculas de minúsculas e servidor B que não diferencia maiúsculas de minúsculas: a ordem de classificação do servidor A pode diferenciar maiúsculas de minúsculas e a ordem de classificação do servidor B pode não diferenciar maiúsculas de minúsculas. Nesse caso, os usuários não podem fazer login 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 do servidor A e do servidor B pode diferenciar maiúsculas de minúsculas ou a ordem de classificação do servidor A e do servidor B pode não diferenciar maiúsculas de minúsculas. Nesses casos, os usuários não enfrentam problemas.

Como lidar com logins já existentes no servidor de destino

O script foi projetado para verificar se o logon existe no servidor de destino e criar um logon somente se não existir. No entanto, se você receber a seguinte mensagem de erro ao executar o script de saída na instância no servidor B, será necessário resolvê-la manualmente 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 logon que já está na instância no servidor B pode ter um SID igual a um SID no script de saída. 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 Sid de parâmetro fornecido está em uso.

Para resolver o problema manualmente, siga estas etapas:

Examine o script de saída com cuidado.
Examine o sys.server_principals conteúdo da exibição na instância no servidor B.
Solucione essas mensagens de erro conforme apropriado.

A partir do SQL Server 2005, o SID de um logon é usado para gerenciar o acesso no nível do banco de dados. Ocasionalmente, um logon pode ter SIDs diferentes quando mapeado para usuários em bancos de dados diferentes. Esse problema pode ocorrer se os bancos de dados forem combinados manualmente de servidores diferentes. Nesses casos, o logon só pode acessar o banco de dados em que o SID da entidade de segurança do banco de dados corresponde ao sys.server_principals SID na exibição. Para resolver esse problema, remova manualmente o usuário do banco de dados com o SID incompatível usando a instrução DROP USER . Em seguida, adicione o usuário novamente com a CREATE USER instrução e mapeie-o para o logon correto (entidade de segurança do servidor).

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

Compartilhar via

Transferir logons e senhas entre instâncias do SQL Server

Introdução

Por que transferir logons entre instâncias do SQL Server?

Etapas para transferir os logins

Método 1: gerar scripts por meio do SSMS no servidor de origem e redefinir manualmente as senhas para logons do SQL Server no servidor de destino

Método 2: Transferir logins e senhas para o servidor de destino (Servidor B) usando scripts gerados no servidor de origem (Servidor A)

Comentários

Informações de hash de senha

Como lidar com a mudança de domínios

Permissões para visualizar e selecionar todos os logins

A configuração padrão do banco de dados não é roteirizada e transferida

Como lidar com diferentes ordens de classificação entre os servidores de origem e de destino

Como lidar com logins já existentes no servidor de destino

Referências

Comentários

Recursos adicionais