Compartilhar via

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

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsAnalytics Platform System (PDW)

Os usuários ficam órfãos no SQL Server quando um usuário de banco de dados é baseado em um logon no master banco de dados, mas o logon não existe mastermais. Isso pode ocorrer quando o logon é excluído ou quando o banco de dados é movido para outro servidor no qual o logon não existe. Este artigo descreve como encontrar usuários órfãos e remapeá-los para logons.

Observação

Reduza a possibilidade de usuários órfãos usando usuários de banco de dados independente para os bancos de dados que podem ser movidos. Para obter mais informações, consulte Torne seu banco de dados portátil usando bancos de dados independentes.

Segundo plano

Para conexões com um banco de dados em uma instância do SQL Server que usa uma entidade de segurança (identidade do usuário do banco de dados) com base em um logon, a entidade de segurança deve ter um logon válido no master banco de dados. Esse logon é usado no processo de autenticação que verifica a identidade da entidade de segurança e determina se a entidade de segurança tem permissão para se conectar à 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 um "usuário de banco de dados" 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 independentes se autenticam no nível do banco de dados do usuário e não estão associados a logons. Esse modelo é recomendado porque os bancos de dados são mais portáteis e os usuários de banco de dados independentes não podem ficar órfãos. No entanto, eles devem ser recriados para cada banco de dados. Esse modelo pode ser impraticável em um ambiente que tenha muitos bancos de dados.

  • A conta de convidado

    Quando habilitada em um banco de dados, essa conta permite que logons do SQL Server que não são 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ções 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 o usuário do Windows for membro de um grupo do Windows que também é um usuário no banco de dados.

Informações sobre o mapeamento de um logon do SQL Server para um usuário de banco de dados são armazenadas no banco de dados. Ele inclui o nome do usuário do banco de dados e o identificador de segurança (SID) do logon correspondente do SQL Server. 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 correspondente do SQL Server é indefinido ou definido incorretamente em uma instância de servidor não pode entrar na instância. Esse usuário é um usuário órfão do banco de dados nessa instância do servidor. A órfã poderá acontecer se o usuário do banco de dados for mapeado para um logon SID que não esteja presente no master banco de dados. 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 que o logon seja recriado, ele terá um diferente SID, portanto, o usuário do banco de dados ainda ficará ó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 ausentes do SQL Server, 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 sids correspondentes no banco de dados atual que não estão vinculados a nenhum logon do SQL Server.

Para o Banco de Dados SQL do Azure e o Azure Synapse Analytics

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

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

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

  2. Conecte-se ao banco de dados do usuário e examine os SIDs dos usuários na sys.database_principals tabela 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 de banco de dados sys.database_principals do usuário que não são correspondidas por SIDs de logon na tabela de master banco de dados sql_logins .

Resolver um usuário órfão

master No banco de dados, use a instrução CREATE LOGIN com a opção SID de recriar um logon ausente. Forneça o SID usuário do banco de dados obtido 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 que já existe master, 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>;

Quando você recria 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 ALTER LOGIN instrução:

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

Importante

Qualquer logon pode alterar a própria senha. Somente logons com a permissão ALTER ANY LOGIN podem alterar a senha de logon de outro usuário. Porém, somente membros da função sysadmin podem modificar senhas de membros da função sysadmin .

Conteúdo relacionado

  • Last updated on