Solução de problemas de usuários órfãos (SQL Server)Troubleshoot Orphaned Users (SQL Server)

APLICA-SE A: simSQL Server simBanco de Dados SQL do Azure simAzure Synapse Analytics (SQL DW) simParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

Os usuários órfãos no SQL ServerSQL 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.Orphaned users in SQL ServerSQL Server occur when a database user is based on a login in the master database, but the login no longer exists in master. Isso pode ocorrer quando o logon é excluído, ou quando o banco de dados é movido para outro servidor onde o logon não existe.This can occur when the login is deleted, or when the database is moved to another server where the login does not exist. Este tópico descreve como localizar usuários órfãos e remapeá-los para logons.This topic describes how to find orphaned users, and remap them to logins.

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.Reduce the possibility of orphaned users by using contained database users for databases that might be moved. Para obter mais informações, consulte Usuários de bancos de dados independentes – Tornando seu banco de dados portátil.For more information, see Contained Database Users - Making Your Database Portable.

Plano de fundoBackground

Para se conectar a um banco de dados em uma instância do SQL ServerSQL 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 .To connect to a database on an instance of SQL ServerSQL Server using a security principal (database user identity) based on a login, the principal must have a valid login in the master database. 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 ServerSQL Server.This login is used in the authentication process that verifies the principals identity and determines if the principal is allowed to connect to the instance of SQL ServerSQL Server. Os logons do SQL ServerSQL 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 .The SQL ServerSQL Server logins on a server instance are visible in the sys.server_principals catalog view and the sys.sql_logins compatibility view.

SQL ServerSQL Server acessam bancos de dados individuais como um "usuário do banco de dados" mapeado para o logon do SQL ServerSQL Server .logins access individual databases as "database user" that is mapped to the SQL ServerSQL Server login. Há três exceções a essa regra:There are three exceptions to this rule:

  • Usuários de banco de dados independenteContained database users

    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.Contained database users authenticate at the user-database level and are not associated with logins. 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.This is recommended because the databases are more portable and contained database users cannot become orphaned. No entanto, eles devem ser recriados para cada banco de dados.However they must be recreated for each database. Isso pode ser impraticável em um ambiente com muitos bancos de dados.This might be impractical in an environment with many databases.

  • A conta de convidado .The guest account.

    Quando habilitada no banco de dados, essa conta permite que os logons do SQL ServerSQL Server que não estejam mapeados para um usuário de banco de dados acessem o banco de dados como o usuário convidado .When enabled in the database, this account permits SQL ServerSQL Server logins that are not mapped to a database user to enter the database as the guest user. A conta de convidado está desabilitada por padrão.The guest account is disabled by default.

  • Associação de grupo do Microsoft Windows.Microsoft Windows group memberships.

    Um logon do SQL ServerSQL 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.A SQL ServerSQL Server login created from a Windows user can enter a database if the Windows user is a member of a Windows group that is also a user in the database.

Informações sobre o mapeamento de um logon do SQL ServerSQL Server para um usuário do banco de dados são armazenadas no banco de dados.Information about the mapping of a SQL ServerSQL Server login to a database user is stored within the database. Isso inclui o nome do usuário do banco de dados e o SID do logon do SQL ServerSQL Server correspondente.It includes the name of the database user and the SID of the corresponding SQL ServerSQL Server login. As permissões desse usuário de banco de dados são aplicadas para autorização no banco de dados.The permissions of this database user are applied for authorization in the database.

Um usuário de banco de dados (com base em um logon) para o qual o logon do SQL ServerSQL 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.A database user (based on a login) for which the corresponding SQL ServerSQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance. Esse usuário é um usuário órfão do banco de dados nessa instância do servidor.Such a user is said to be an orphaned user of the database on that server instance. 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 .Orphaning can happen if the database user is mapped to a login SID that is not present in the master instance. 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 ServerSQL Server , na qual o logon nunca foi criado.A database user can become orphaned after a database is restored or attached to a different instance of SQL ServerSQL Server where the login was never created. Um usuário do banco de dados também se tornará órfão se o logon do SQL ServerSQL Server correspondente for descartado.A database user can also become orphaned if the corresponding SQL ServerSQL Server login is dropped. Mesmo se o logon for recriado, ele terá um SID diferente, então o usuário do banco de dados ainda será órfão.Even if the login is recreated, it will have a different SID, so the database user will still be orphaned.

Para detectar usuários órfãosTo Detect Orphaned Users

Para SQL ServerSQL Server e PDWFor SQL ServerSQL Server and PDW

Para detectar usuários órfãos no SQL ServerSQL Server com base em logons de autenticação do SQL ServerSQL Server ausentes, execute a seguinte instrução no banco de dados do usuário:To detect orphaned users in SQL ServerSQL Server based on missing SQL ServerSQL Server authentication logins, execute the following statement in the user database:

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 authentication_type_desc = 'INSTANCE';  

A saída lista os usuários de autenticação do SQL ServerSQL Server e as SIDs (IDs de segurança) correspondentes no banco de dados atual que não estão vinculados a um logon do SQL ServerSQL Server .The output lists the SQL ServerSQL Server authentication users and corresponding security identifiers (SID) in the current database that are not linked to any SQL ServerSQL Server login.

Para Banco de Dados SQL e SQL Data WarehouseFor SQL Database and SQL Data Warehouse

A tabela sys.server_principals não está disponível no Banco de Dados SQL ou no SQL Data Warehouse.The sys.server_principals table is not available in SQL Database or SQL Data Warehouse. Identifique usuários órfãos nesses ambientes com as seguintes etapas:Identify orphaned users in those environments with the following steps:

  1. Conecte-se ao banco de dados master e selecione as SIDs para os logons com a seguinte consulta:Connect to the master database and select the SID's for the logins with the following query:

    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:Connect to the user database and review the SID's of the users in the sys.database_principals table, by using the following query:

    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.Compare the two lists to determine if there are user SID's in the user database sys.database_principals table which are not matched by login SID's in the master database sql_logins table.

Para resolver um usuário órfãoTo Resolve an Orphaned User

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:In the master database, use the CREATE LOGIN statement with the SID option to recreate a missing login, providing the SID of the database user obtained in the previous section:

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.To map an orphaned user to a login which already exists in master, execute the ALTER USER statement in the user database, specifying the login name.

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.When you recreate a missing login, the user can access the database using the password provided. Em seguida, o usuário pode alterar a senha da conta de logon usando a instrução ALTER LOGIN.Then the user can alter the password of the login account by using the ALTER LOGIN statement.

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

Importante

Qualquer logon pode alterar sua própria senha.Any login can change it's own password. Somente logons com a permissão ALTER ANY LOGIN podem alterar a senha de logon de outro usuário.Only logins with the ALTER ANY LOGIN permission can change the password of another user's login. Porém, somente membros da função sysadmin podem modificar senhas de membros da função sysadmin .However, only members of the sysadmin role can modify passwords of sysadmin role members.

O procedimento preterido sp_change_users_login também funciona com usuários órfãos.The deprecated procedure sp_change_users_login also works with orphaned users. sp_change_users_login não pode ser usado com Banco de Dados SQLSQL Database.sp_change_users_login cannot be used with Banco de Dados SQLSQL Database.

Consulte TambémSee Also

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