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 utilizadores ficam órfãos no SQL Server quando um utilizador de base de dados se baseia num login na base de dados master, mas o login já não existe em 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.

Contexto geral

Para conexões com um banco de dados numa instância do SQL Server que utilizem uma entidade de segurança (identidade do usuário do banco de dados) baseada num logon, a entidade de segurança deve ter um logon válido na base de dados master. Este logon é usado no processo de autenticação que verifica a identidade do principal e determina se o principal 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 vista de catálogo sys.server_principals e na vista 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. 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. Tal utilizador é um utilizador órfão da base de dados nessa instância do servidor. A orfandade pode acontecer se o utilizador da base de dados estiver mapeado para um logon SID que não está presente na base de dados master. 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, tem um SID, ainda assim o utilizador da base de dados continua ó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. Para identificar utilizadores órfãos nesses ambientes, complete estes passos:

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 utilizador na tabela de sys.database_principals banco de dados de utilizadores que não são correspondidos por SIDs de login na tabela de master banco de dados sql_logins.

Resolver problemas de um utilizador órfão

No banco de dados master, use a instrução CREATE LOGIN com a opção SID para 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>';

Métodos preteridos

Em versões anteriores do SQL Server, o sp_change_users_login procedimento armazenado era usado para resolver utilizadores órfãos. Este procedimento está obsoleto e poderá ser removido numa versão futura. Utilize ALTER USER em substituição.

A tabela seguinte mostra a sintaxe moderna equivalente para operações comuns sp_change_users_login :

Para mais informações, consulte sp_change_users_login (Transact-SQL).

Conteúdo relacionado

• CRIAR LOGIN (Transact-SQL)
• ALTER USER (Transact-SQL)
• CRIAR USUÁRIO (Transact-SQL)
• sys.database_principals (Transact-SQL)
• sys.server_principals (Transact-SQL)
• sys.sql_logins (Transact-SQL)
• sp_change_users_login (Transact-SQL)
• Torne seu banco de dados portátil usando bancos de dados contidos