Usuário órfão no SQL Server, porque ocorre e como resolver?

Fala galera, tudo bem? No post de hoje quero tratar de uma situação que pode ocorrer em um ambiente de banco de dados SQL Server e que em um primeiro momento pode parecer confuso, mas você vai terminar de ler esse post e vai compreender de forma simples o motivo e como resolver =) Bora lá?

Inicialmente você precisa entender que existem dois contextos diferentes no SQL Server quando falamos de acesso: Logins e usuários, podem parecer a mesma coisa mas não são!

O Login esta relacionado ao acesso na instância do SQL Server, nele é onde temos a senha de acesso e ele pode ter permissões a nível de instância (mais voltada ao gerenciamento do ambiente). De outro lado temos o usuário que esta relacionado ao banco de dados que esta dentro da instância, nele temos as permissões a nível de banco de dados (para fazer select, insert, update, criar tabelas e por ai vai).

Um usuário que acessa o banco de dados precisa estar vinculado a um login que conecta na instância.

A imagem abaixo ilustra esse cenário.

Um único login acessa a instância e dentro de cada base de dados existente em que ele precisa ter permissão existe um usuário ciado com a permissão específica vinculado a esse login.

Para saber mais sobre o tema tenho um vídeo no youtube explicando bem detalhadamente, veja aqui.

Agora que você já viu a relação entre eles, vamos ao ponto desse post.

Para simular o problema, vou criar a situação no meu ambiente de estudos, você pode fazer o mesmo ai do outro lado.

Vou criar um banco de dados chamado db_empresa.

CREATE DATABASE [db_empresa]

ON PRIMARY

( NAME = N'db_empresa', FILENAME = N'C:\TMP\DATA\db_empresa.mdf' , SIZE = 32768KB , FILEGROWTH = 65536KB )

LOG ON

( NAME = N'db_empresa_log', FILENAME = N'C:\TMP\DATA\db_empresa_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )

GO

Agora vamos criar um login na instância chamado 'aplic', nesse script estamos criando com a senha 'aplic' (lembre-se que é um ambiente de estudos apenas, em um ambiente oficial no dia a dia, nunca crie um login com senha fraca, conto com você hehe).

USE [master]

GO

CREATE LOGIN [aplic] WITH PASSWORD=N'aplic'

GO

Nesse momento, temos apenas acesso para conexão na instância, não atribuímos nenhuma permissão extra para ele.

O próximo passo é criar um usuário dentro do banco de dados db_empresa vinculando ao login criado anteriormente.

Estou criando um usuário chamado 'aplic' vinculado ao login 'aplic', na sequência adicionamos o usuário dentro da base db_empresa na role db_owner (que te da permissão para executar qualquer ação dentro do banco de dados).

* O vínculo entre os dois é feito por um campo chamado SID que é o identificador único do login (como se fosse um código), o usuário recebe o valor do SID do login internamente para criar o vínculo.

Ao expandir a pasta segurança dentro do banco de dados e clique duas vezes sobre o nome do usuário 'aplic', na aba geral podemos ver o relacionamento entre o usuário e login (conforme imagem abaixo).

Realize o acesso agora com o login que acabou de ser criado (Aplic)

Após acessar a instância podemos visualizar o que fizemos até aqui.

Dentro da base de dados db_empresa vá até a pasta Segurança e depois usuários, você verá o usuário aplic criado conforme a imagem abaixo. Depois vá em segurança no nível de instância, abra a pasta logons e você vai ver o login chamado aplic.

Abra uma nova query e execute o script abaixo (criação de tabela, insert de dados e select).

CREATE TABLE TAB01 (ID SMALLINT IDENTITY(1,1) PRIMARY KEY,

CMP01 VARCHAR(10) NOT NULL,

CMP02 VARCHAR(15) NULL,

CMP03 SMALLDATETIME)

GO

INSERT INTO TAB01 VALUES ('ABCDEFG','HIJKLMNO',GETDATE())

GO

SELECT * FROM TAB01

GO

Podemos ver a tabela criada com sucesso!

Agora vamos ao problema, lembra que o vínculo entre login e usuário é feito pelo SID (código do login) imagine o cenário onde alguém excluiu o login da instância, nesse caso o usuário dentro da base "perdeu o login", na linguagem do dia a dia na área de banco de dados dizemos que ele ficou órfão (perdeu o pai - login ao qual estava referenciando). Outra situação que pode causar isso é quando pegamos um backup e vamos restaurar ele em outra instância onde existe um login com o mesmo nome, porém, o vínculo não é feito pelo nome como já vimos, é feito pelo SID do login, se esse login não foi criado com o mesmo SID (sim, isso é possível com script) da instância original de onde veio esse backup, o usuário da base ao restaurar esse backup nessa instância nova também ficará órfão.

Vamos simular.

Desconecte da instância com o login aplic para fechar as conexões dele e na sequencia abra uma nova query com o login sysadmin da instância (sa ou outro que você tiver no ambiente) e exclua o login conforme abaixo.

DROP LOGIN APLIC

go

Agora vá até o usuário dentro do banco de dados e clique duas vezes sobre ele. Vá na aba geral.

Perceba que agora que excluímos o login não vemos mais ele constando como relacionado ao usuário.

Vamos criar o login novamente com o mesmo nome.

Mesmo criando com o mesmo nome o vínculo não foi criado novamente.

Se eu tentar me conectar com o login eu até consigo, porém ao tentar abrir o banco de dados recebo erro pois ele não esta de fato vinculado ao usuário dentro do banco.

Pela tela não temos como ajustar isso, mas podemos resolver usando um script. Vamos utilizar uma procedure interna do SQL Server chamada sp_change_users_login.

Ela recebe alguns parâmetros:

sp_change_users_login [ @Action = ] 'action' -- Descreve a ação a ser executada: Auto_Fix, Report ou Update_One

, [ @UserNamePattern = ] 'user' -- Nome do usuário envolvido

, [ @LoginName = ] 'login' -- Qual é o nome do login a ser vinculado

, [ @Password = ] 'password' ; -- Senha do login (usado quando a ação escolhida é Auto_Fix apenas)

Para o nosso cenário vamos usar o seguinte script, onde a ação é Update_one, o nome do usuário é aplic e o nome do login também é aplic.

USE db_empresa

GO

EXEC sp_change_users_login 'Update_One', 'aplic', 'aplic';

GO

Ao olharmos novamente as propriedades do usuário podemos ver novamente o vínculo criado entre eles.

Agora ao se conectar com o login aplic e tentar abrir o banco de dados db_empresa não teremos mais problemas.

A mesma solução vale para o caso do restore do backup em outro servidor que comentei mais acima.

Obs: Você pode usar a proc para levantar quais os usuários em um banco de dados estão sem o vinculo correto com o seu login conforme script abaixo, usamos o parâmetro 'Report'.

use db_empresa

go

EXEC sp_change_users_login 'Report'

go

Nesse post você aprendeu o conceito de usuário órfão no SQL Server, quais os cenários em que isso pode ocorrer e por fim como ajustar essa situação.

Gostou do post? Compartilha com seus colegas que querem ser um DBA SQL Server!

Nos acompanhe em nossas redes sociais!

Grupo VIP Telegram: DBA On boarding

Youtube(conteúdo diário): DBA On boarding

Face & Instagram(conteúdo diário): DBA On boarding

Até a próxima, tchau!

#comunidadebd #iniciativadba