Transferir logons e senhas entre instâncias do SQL Server

Artigo
09/06/2024

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.

Observação

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

Mais informações

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

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

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

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

Observação

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

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

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

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

Observação

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

Método 2: transferir logons 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 nomeados 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.

Importante

Examine as informações na seção Comentários a seguir antes de prosseguir com a implementação de etapas no servidor de destino.

Etapas no servidor de destino (Servidor B)

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

Comentários

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

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.
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ê precisa substituir o nome de domínio original usando o novo nome de domínio nas CREATE LOGIN 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 resolve 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.
No script de saída, os logons são criados usando a senha criptografada. Isso ocorre devido ao argumento HASHED na instrução CREATE LOGIN. Esse argumento especifica que a senha inserida após o argumento PASSWORD já esteja com hash.
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 podem criar ou executar o script de saída.
As etapas deste artigo não transferem as informações padrão do banco de dados 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.
Classificar pedidos em servidores de origem e de destino:
- Servidor insensível a caso A e servidor com confidencialidade de caso B: a ordem de classificação do servidor A pode ser insensibilidade de caso e a ordem de classificação do servidor B pode ser sensível a casos. 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 sensível a casos A e servidor sem maiúsculas de maiúsculas de maiúsculas de minúsculas B: A ordem de classificação do servidor A pode ser sensível a casos e a ordem de classificação do servidor B pode ser insensibilidade de caso. Nesse caso, os usuários não podem fazer logon 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.
- Sensível a maiúsculas de maiúsculas ou minúsculas em ambos os servidores: a ordem de classificação do servidor A e do servidor B pode ser sensível a casos ou a ordem de classificação do servidor A e do servidor B pode ser insensível a casos. Nesses casos, os usuários não enfrentam um problema.
Um logon que já está na instância no servidor B pode ter um nome que é o mesmo que um nome 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:

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 que é o mesmo que 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.

Portanto, você precisa fazer o seguinte:
1. Examine o script de saída com cuidado.
2. Examine o conteúdo da sys.server_principals exibição na instância no servidor B.
3. Solucione essas mensagens de erro conforme apropriado.
  
  No SQL Server 2005, o SID de um logon é usado para implementar o acesso no nível do banco de dados. Um logon pode ter SIDs diferentes em bancos de dados diferentes em um servidor. Nesse caso, o logon só pode acessar o banco de dados que tem o SID que corresponde ao SID na exibição sys.server_principals. Esse problema poderá 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ção DROP USER. Em seguida, adicione o logon novamente usando a instrução CREATE USER.

Compartilhar via

Transferir logons e senhas entre instâncias do SQL Server

Introdução

Mais informações

Etapas no servidor de destino (Servidor B)

Comentários

Referências

Comentários

Recursos adicionais