Partilhar via

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

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

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

Observação

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

Contexto geral

Para conexões com um banco de dados em uma instância do SQL Server que usam 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 tem permissão para se conectar à instância do SQL Server. Os logons do SQL Server em uma instância de servidor são visíveis no modo de exibição de catálogo sys.server_principals e no modo de exibição de compatibilidade do 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. Existem três exceções a esta regra:

  • Utilizadores contidos na base de dados

    Os usuários do banco de dados contidos são autenticados 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 contidos 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 tem 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á desativada por padrão.

  • Associações de grupo do Microsoft Windows

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

As 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. 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 do 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 está indefinido ou incorretamente definido em uma instância do servidor não pode entrar na instância. Diz-se que esse usuário é um usuário órfão do banco de dados nessa instância do servidor. A orfandade pode acontecer se o usuário do banco de dados for mapeado para um logon SID que não está presente no master banco de dados. Um usuário de banco de dados pode ficar órfão depois que um banco de dados é restaurado ou anexado a uma instância diferente do SQL Server onde o logon nunca foi criado. Um usuário de banco de dados também pode ficar órfão se o logon correspondente do SQL Server for descartado. Mesmo que o login seja recriado, ele terá um , SIDportanto, o usuário do banco de dados ainda ficará órfão.

Detetar usuários órfãos

Para SQL Server e PDW

Para detetar 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 de usuários:

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 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 de usuários e revise 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 de usuários que não são correspondidos por SIDs de login na tabela de master banco de dados sql_logins .

Resolver um utilizador órfão

master No banco de dados, use a instrução CREATE LOGIN com a SID opção de recriar um login ausente. Forneça o SID do usuário do banco de dados que você obteve 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 no master, execute a instrução ALTER USER no banco de dados do usuário, especificando o nome de login:

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

Quando você recria um login ausente, o usuário pode acessar o banco de dados usando a senha fornecida. O usuário pode então alterar a senha da conta de login usando a ALTER LOGIN instrução:

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

Importante

Qualquer login pode alterar sua própria senha. Somente logins com a ALTER ANY LOGIN permissão podem alterar a senha do login de outro usuário. No entanto, apenas membros da função sysadmin podem modificar senhas de membros da função sysadmin .

Conteúdo relacionado

  • Last updated on