Corrigindo o problema de usuários órfãos no SQL Server

Motivação

Quando se realiza a migração de um banco de dados SQL Server para um novo servidor, é possível que após a restauração do backup alguns usuários fiquem sem um login associado, problema que pode deixá-los sem acesso às bases de dados. Esses usuários são então chamados de órfãos e por não entender esse problema, algumas vezes os responsáveis pelo banco de dados acabam criando novas credenciais para voltar a ter acesso aos bancos de dados.

Neste artigo, veremos como identificar esses usuários e como corrigir esse problema.

Saiba mais sobre: SQL Server

Contextualização: Entendendo Logins e Usuários

Login é um objeto que garante acesso em nível de instância do SQL Server. Para listar todos os logins existentes, podemos utilizar o seguinte script:

SELECT * FROM SYS.server_principals

Já um usuário fornece acesso aos objetos em nível de banco de dados no SQL Server, e pode ou não estar vinculado a um login, dependendo da política do seu ambiente. Para listar todos os usuários de um banco de dados, podemos realizar a seguinte consulta:

SELECT * FROM SYS.sysusers

Ao criarmos um login no SQL Server, ele recebe automaticamente uma identificação, chamada de SID e que estará armazenada na tabela SYS.sysusers, informando a qual login pertence determinado usuário. Assim, podemos fazer uma junção entre as tabelas vistas acima, com a instrução da Listagem 1, e listar os usuários com suas respectivas associações aos logins. O resultado pode ser visto na Figura 1.

        SELECT U.name as USUARIO
        , L.name AS LOGIN
        , U.sid AS SID_USER
        , L.sid AS SID_LOGIN
        FROM SYS.sysusers U
        INNER JOIN SYS.server_principals L
        ON U.sid = L.sid
        WHERE L.name = "lg_devmedia" -- Filtro aqui para retornar somente 1 Login.
        

Esses mesmos dados também podem ser verificados no SQL Server Management Studio ao abrir o nó Databases, expandir a opção Security e clicar com o botão direito do mouse em Users > Properties. Na aba General, que é exibida na Figura 2, vemos os detalhes dos usuários.

Saiba mais sobre: logins, usuários e permissões no SQL Server

Problema: Entendendo o usuário órfão

Conhecendo os conceitos de usuários órfãos e como identificá-los, podemos entender agora o cenário em que esse problema surge: ao realizar um backup, o SQL Server carrega apenas os objetos do banco de dados (e não da instância inteira). Assim, os usuários são incluídos no arquivo, mas os logins, não.

Na Figura 3 temos um exemplo de ambiente com duas instâncias (a principal em vermelho e a que receberá o backup em verde) e a base DEVMEDIA que será migrada.

Após um backup no servidor antigo e um restore no novo, verificaremos nas tabelas de usuários e logins uma nova situação para o usuário u_devmedia que, de acordo com a Figura 1, possuía um login associado normalmente. Agora, no entanto, ao realizarmos a consulta da Listagem 2, veremos que o usuário se tornou órfão, pois os campos LOGIN e SID_LOGIN estão nulos, como mostra a Figura 4.

        SELECT U.name as USUARIO
        , L.name AS LOGIN
        , U.sid AS SID_USER
        , L.sid AS SID_LOGIN
        FROM SYS.sysusers U
        LEFT JOIN SYS.server_principals L
        ON U.sid = L.sid
        WHERE u.name = "u_devmedia"
        

Saiba mais sobre: Como realizar backups no SQL Server

Solução 1: Associando o usuário a um login existente

Nesta primeira forma de resolver o problema, vamos criar um login e depois associá-lo ao usuário órfão, utilizando para isso o script da Listagem 3.

        CREATE LOGIN [lg_devmedia] WITH PASSWORD=N"1",
        CHECK_EXPIRATION=OFF,
        CHECK_POLICY=OFF
        GO
        

• Linha 2: a cláusula CHECK_EXPIRATION=OFF informa que a senha não irá expirar;
• Linha 3: a cláusula CHECK_POLICY=OFFdiz que a senha não precisará ter uma política de segurança forte.

Criado o login, precisamos apenas associá-lo a um usuário utilizando o seguinte script:

        ALTER USER u_devmedia WITH LOGIN = [lg_devmedia]
        

Solução 2: Associando o SID do usuário ao login

Nesta segunda forma, iremos pegar o SID da tabela SYS.sysusers (que pode ser obtido com o script da Listagem 2) e acrescentá-lo já na criação do login, conforme a Listagem 4.

        CREATE LOGIN [lg_devmedia] WITH PASSWORD=N"1",
        CHECK_EXPIRATION=OFF,
        CHECK_POLICY=OFF,
        SID = 0x902B3E8B5639F644AC70976096AB9B73 --SID do usuário
        GO
        

Feito isso, execute novamente a consulta da Listagem 1 e verifique que agora a associação entre usuário e login foi reestabelecida, garantindo o pleno acesso às bases de dados.