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_principalsJá 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.sysusersAo 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.
