Solucionar problemas de usuários órfãos (SQL Server)

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics Analytics Platform System (PDW)

Os usuários órfãos no SQL Server ocorrem quando um usuário de banco de dados tem base em um logon no banco de dados mestre, mas o logon não existe mais no mestre. Isso pode ocorrer quando o logon é excluído, ou quando o banco de dados é movido para outro servidor onde o logon não existe. Este tópico descreve como localizar usuários órfãos e remapeá-los para logons.

Segundo plano

Para se conectar a um banco de dados em uma instância do SQL Server usando uma entidade de segurança (identidade do usuário do banco de dados) com base em um logon, a entidade deve ter um logon válido no banco de dados mestre . Esse logon é usado no processo de autenticação que verifica a identidade da entidade e determina se ela tem permissão para conectar-se à instância do SQL Server. Os logons do SQL Server em uma instância do servidor são visíveis na exibição do catálogo sys.server_principals e na exibição de compatibilidade sys.sql_logins .

Os logons do SQL Server acessam bancos de dados individuais como "usuário do banco de dados" que é mapeado para o logon do SQL Server. Há três exceções a essa regra:

• Usuários de banco de dados independente

  Os usuários de banco de dados independente se autenticam no nível do banco de dados de usuário e não estão associados a logons. Isso é recomendado porque os bancos de dados são mais portáteis e os usuários de banco de dados independente não podem se tornar órfãos. No entanto, eles devem ser recriados para cada banco de dados. Isso pode ser impraticável em um ambiente com muitos bancos de dados.

• A conta de convidado .

  Quando habilitada no banco de dados, essa conta permite que os logons do SQL Server que não estejam mapeados para um usuário de banco de dados acessem o banco de dados como o usuário convidado . A conta de convidado está desabilitada por padrão.

• Associação de grupo do Microsoft Windows.

  Um logon do SQL Server criado a partir de um usuário do Windows poderá acessar um banco de dados se esse usuário for membro de um grupo do Windows que também seja um usuário no banco de dados.

Informações sobre o mapeamento de um logon do SQL Server para um usuário do banco de dados são armazenadas no banco de dados. Isso inclui o nome do usuário do banco de dados e o SID do logon do SQL Server correspondente. As permissões desse usuário de banco de dados são aplicadas para autorização no banco de dados.

Um usuário de banco de dados (com base em um logon) para o qual o logon do SQL Server correspondente não esteja definido, ou que esteja definido incorretamente em uma instância do servidor, não pode fazer logon na instância. Esse usuário é um usuário órfão do banco de dados nessa instância do servidor. A condição de órfão pode ocorrer se o usuário do banco de dados for mapeado para um SID de logon que não esteja presente na instância do master . Um usuário de banco de dados pode se tornar órfão após um banco de dados ser restaurado ou anexado a uma instância diferente do SQL Server , na qual o logon nunca foi criado. Um usuário do banco de dados também se tornará órfão se o logon do SQL Server correspondente for descartado. Mesmo se o logon for recriado, ele terá um SID diferente, então o usuário do banco de dados ainda será órfão.

Detectar usuários órfãos

Para SQL Server e PDW

Para detectar usuários órfãos no SQL Server com base em logons de autenticação do SQL Server ausentes, execute a seguinte instrução no banco de dados do usuário:

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';  

A saída lista os usuários de autenticação do SQL Server e os SID (identificadores de segurança) correspondentes no banco de dados atual que não estão vinculados a nenhum logon do SQL Server.

Para o Banco de Dados SQL e o Azure Synapse Analytics

A tabela sys.server_principals não está disponível no Banco de Dados SQL nem no Azure Synapse Analytics. Identifique usuários órfãos nesses ambientes com as seguintes etapas:

1. Conecte-se ao banco de dados master e selecione as SIDs para os logons com a seguinte consulta:

   SELECT sid 
   FROM sys.sql_logins 
   WHERE type = 'S'; 
   

2. Conecte-se ao banco de dados de usuário e examine as SIDs dos usuários na tabela sys.database_principals usando a seguinte consulta:

   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 duas listas para determinar se há SIDs de usuário na tabela sys.database_principals do banco de dados do usuário que não correspondem às SIDs de logon na tabela sql_logins do banco de dados mestre.

Resolver um usuário órfão

No banco de dados mestre, use a instrução CREATE LOGIN com a opção de SID para recriar um logon ausente, fornecendo a SID do usuário do banco de dados obtida na seção anterior:

CREATE LOGIN <login_name>   
WITH PASSWORD = '<use_a_strong_password_here>',  
SID = <SID>;  

Para mapear um usuário órfão para um logon já existente no mestre, execute a instrução ALTER USER no banco de dados do usuário, especificando o nome de logon.

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

Ao recriar um logon ausente, o usuário pode acessar o banco de dados usando a senha fornecida. Em seguida, o usuário pode alterar a senha da conta de logon usando a instrução ALTER LOGIN.

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

Consulte Também

CREATE LOGIN (Transact-SQL)
ALTER USER (Transact-SQL)
CREATE USER (Transact-SQL)
sys.database_principals (Transact-SQL)
sys.server_principals (Transact-SQL)
sp_change_users_login (Transact-SQL)
sp_addlogin (Transact-SQL)
sp_grantlogin (Transact-SQL)
sp_password (Transact-SQL)
sys.sysusers (Transact-SQL)
sys.sql_loginssys.syslogins (Transact-SQL)