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

Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Para efetuar o download você precisa estar logado. Clique aqui para efetuar o login
Confirmar voto
0
 (7)  (0)

Veja neste artigo como corrigir no SQL Server um usuário órfão, problema comum quando se migra bases de dados entre instâncias/servidores de bancos de dados.

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 o 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.
Listagem 1. Retornando logins e usuários associados pelo SID Figura 1. Associação Login x Usuário via T-SQL

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.

Figura 2. Associação de login e usuário no SQL Server Management Studio

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.

Figura 3. Ambiente para simular o problema

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'
Listagem 2. Verificando a não associação de um login ao usuário Figura 4. Usuário órfão

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.

01 CREATE LOGIN [lg_devmedia] WITH PASSWORD=N'1',
02 CHECK_EXPIRATION=OFF,
03 CHECK_POLICY=OFF
04 GO
Listagem 3. Criando um novo Login

Linha 2: a cláusula CHECK_EXPIRATION=OFF informa que a senha não irá expirar;

Linha 3: a cláusula CHECK_POLICY=OFF diz 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
Listagem 4. Criando login com o SID do usuário

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.

 
Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Receba nossas novidades
Ficou com alguma dúvida?