Gerenciando Usuários Orfãos no SQL Server: Um Guia Abrangente

Gerenciar usuários órfãos no SQL Server é crítico para a segurança e administração do banco de dados. Usuários órfãos ocorrem quando um usuário de banco de dados existe sem um login correspondente no banco de dados master. Essa incompatibilidade pode interromper o acesso e levar a potenciais vulnerabilidades de segurança. Neste artigo, exploraremos o que são usuários órfãos, como detectá-los e métodos para resolvê-los, incluindo exemplos e cenários atualizados.

Contexto

Para conectar-se a um banco de dados SQL Server, um usuário deve ter um login válido no banco de dados master. Esse login autentica o usuário e mapeia para um usuário do banco de dados para autorização. O mapeamento é mantido usando o Identificador de Segurança (SID), garantindo que os usuários do banco de dados e os logins do servidor estejam vinculados corretamente.

Exceções à regra de mapeamento login-usuário incluem:

1. Usuários de Banco de Dados Contidos: Autenticam-se no nível do banco de dados, eliminando a dependência de logins do servidor. Esses usuários aprimoram a portabilidade do banco de dados, mas exigem recriação separada para cada banco de dados.
2. Contas Convidadas: Ativadas por padrão, permitem acesso a usuários sem mapeamento explícito, mas geralmente são desaconselhadas por motivos de segurança.
3. Associações a Grupos do Microsoft Windows: Membros de grupos do Windows podem acessar bancos de dados se o grupo for adicionado como usuários.

Um usuário de banco de dados torna-se órfão quando:

• O login correspondente é excluído.
• O banco de dados é migrado ou restaurado sem os logins associados.
• O mapeamento de SID entre o usuário e o login está incorreto.

O que são usuários órfãos?

Um usuário órfão ocorre quando:

• O usuário do banco de dados existe, mas o login correspondente no banco de dados master não existe.
• O SID do usuário do banco de dados e o SID do login não coincidem.

Esses cenários interrompem a autenticação e autorização, levando a erros ou acesso restrito.

Detectando usuários órfãos

Para SQL Server

Use a seguinte consulta para identificar usuários órfãos:

SELECT dp.type_desc, dp.sid, dp.name AS user_name  

FROM sys.database_principals AS dp  

LEFT JOIN sys.server_principals AS sp  

    ON dp.sid = sp.sid  

WHERE sp.sid IS NULL  

    AND dp.authentication_type_desc = 'INSTANCE';

Para Azure SQL Database ou Synapse Analytics

1. Recupere os SIDs dos logins no banco de dados master:

SELECT sid 

FROM sys.sql_logins 

WHERE type = 'S'; 

2. Recupere os SIDs dos usuários no banco de dados de destino:

SELECT name, sid, principal_id

FROM sys.database_principals 

WHERE type = 'S' 

  AND name NOT IN ('guest', 'INFORMATION_SCHEMA', 'sys')

  AND authentication_type_desc = 'INSTANCE';

3. Compare as listas para identificar discrepâncias.

Resolvendo usuários órfãos

1. Recriar login ausente com SID 

Se o login foi excluído:

CREATE LOGIN <login_name>   

WITH PASSWORD = '<use_a_strong_password_here>',  

SID = <SID>;

2. Mapear um usuário órfão a um login existente 

Se o login existir, mas não estiver mapeado:

ALTER USER <user_name> WITH LOGIN = <login_name>;

3. Alterar senha do usuário 

Após recriar o login, atualize sua senha:

ALTER LOGIN <login_name> WITH PASSWORD = '<enterStrongPasswordHere>';

Usando sp_FindOrphanedUser

A stored procedure sp_FindOrphanedUser é uma ferramenta poderosa para detectar e resolver usuários órfãos. Ela fornece:

1. Uma lista de usuários órfãos.
2. Scripts gerados automaticamente para reparos.
3. Detalhes sobre esquemas pertencentes a usuários órfãos.

Obtenha o código do pacote de sp_FindOrphanedUser.

Executando o Procedimento

Execução padrão:

EXEC sp_FindOrphanedUser;

Para um banco de dados específico:

EXEC sp_FindOrphanedUser @DatabaseName = '<DatabaseName>';

Exemplos de Cenários de Usuário Órfão

Exemplo 1: Usuário Sem Login

Cenário

Um login é excluído, deixando o usuário do banco de dados órfão.

• Detectar o usuário órfão:
  MS SQL

  EXEC sp_FindOrphanedUser;

• Recriar o login:
  MS SQL

  CRIAR LOGIN [User1] COM SENHA = 'SenhaSegura123';

  USAR [ExampleDB];

  ALTERAR USUÁRIO [User1] COM LOGIN = [User1];

Exemplo 2: Incompatibilidade de SID

Cenário

A SID de login não corresponde à SID do usuário.

• Detectar a incompatibilidade:
  MS SQL

  EXEC sp_FindOrphanedUser @DatabaseName = 'ExampleDB';

• Corrija o mapeamento SID:
  MS SQL

   

  USE [ExampleDB];

  EXEC sp_change_users_login 'UPDATE_ONE', 'User2', 'User2';

Exemplo 3: Usuário Órfão Possuindo um Esquema

Cenário

Usuário órfão impede a exclusão do esquema.

• Detectar propriedade de esquema:
  MS SQL

   

  EXEC sp_FindOrphanedUser @DatabaseName = 'ExampleDB';

• Reatribuir propriedade do esquema:
  MS SQL

   

  USE [ExampleDB];

  ALTER AUTHORIZATION ON SCHEMA::[User3Schema] TO [dbo];

  DROP USER [User3];

Melhores Práticas

• Use Usuários de Banco de Dados Contidos: Evite dependência em logins do servidor.
• Sincronize Logins: Sempre migre ou restaure logins durante migrações de banco de dados.
• Auditorias Regulares: Verifique periodicamente a existência de usuários órfãos para manter a segurança.

Conclusão

Gerenciar usuários órfãos garante a segurança do banco de dados e a eficiência operacional. Usando consultas e ferramentas como sp_FindOrphanedUser, você pode identificar e resolver problemas rapidamente, mantendo o acesso sem interrupções para usuários legítimos. A adoção de melhores práticas, como usuários contidos e auditorias regulares, pode prevenir tais problemas no futuro.