孤立用户故障排除 (SQL Server)Troubleshoot Orphaned Users (SQL Server)

适用对象:yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

当数据库用户是基于 SQL ServerSQL Server数据库中的登录名,但该登录名在数据库中已不存在时, 中会出现孤立用户。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. 当删除登录名,或者将数据库移动到另一台不存在该登录名的服务器时会出现这种情况。This can occur when the login is deleted, or when the database is moved to another server where the login does not exist. 本主题介绍如何找到孤立用户,并将它们重新映射到登录名。This topic describes how to find orphaned users, and remap them to logins.

备注

为可能被移动的数据库使用包含的数据库用户可以减少形成孤立用户的可能性。Reduce the possibility of orphaned users by using contained database users for databases that might be moved. 有关详细信息,请参阅 包含的数据库用户 - 使你的数据库可移植For more information, see Contained Database Users - Making Your Database Portable.

背景Background

若要使用基于登录名的安全主体(数据库用户标识)连接到 SQL ServerSQL Server 实例上的数据库,则该主体必须在 master 数据库中拥有有效的登录名。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. 在身份验证过程中会使用此登录名,以验证主体身份并确定是否允许将主体连接到 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. 可在 SQL ServerSQL Server sys.server_principals 目录视图和 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 登录名作为映射到 SQL ServerSQL Server 登录名的“数据库用户”访问各个数据库。 logins access individual databases as "database user" that is mapped to the SQL ServerSQL Server login. 此规则有三种例外情况:There are three exceptions to this rule:

  • 包含的数据库用户Contained database users

    包含的数据库用户在用户数据库级别进行身份验证,与登录名无关联。Contained database users authenticate at the user-database level and are not associated with logins. 建议采用这种做法,因为数据库更易于移植,包含的数据库用户不会孤立。This is recommended because the databases are more portable and contained database users cannot become orphaned. 但是必须为每个数据库重新创建用户。However they must be recreated for each database. 在包含多个数据库的环境中,这可能不切实际。This might be impractical in an environment with many databases.

  • guest 帐户。The guest account.

    在数据库中启用后,此帐户允许未映射到数据库用户的 SQL ServerSQL Server 登录名作为 guest 用户进入数据库。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. 默认情况下, guest 帐户是禁用的。The guest account is disabled by default.

  • Microsoft Windows 组成员身份。Microsoft Windows group memberships.

    如果某 Windows 用户是 Windows 组的成员,并且此组也是数据库中的用户,则基于该 Windows 用户创建的 SQL ServerSQL Server 登录名可以进入数据库。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.

    有关 SQL ServerSQL Server 登录名与数据库用户的映射关系的信息存储在数据库中。Information about the mapping of a SQL ServerSQL Server login to a database user is stored within the database. 其中包括数据库用户的名称以及对应 SQL ServerSQL Server 登录名的 SID。It includes the name of the database user and the SID of the corresponding SQL ServerSQL Server login. 该数据库用户的权限用于在数据库中进行授权。The permissions of this database user are applied for authorization in the database.

    在服务器实例上未定义或错误定义了其相应 SQL ServerSQL Server 登录名的数据库用户(基于登录名)无法登录到该实例。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. 这样的用户被称为此服务器实例上的数据库的“孤立用户” 。Such a user is said to be an orphaned user of the database on that server instance. 如果数据库用户映射到 master 实例中不存在的登录名 SID,则该用户可能变为孤立用户。Orphaning can happen if the database user is mapped to a login SID that is not present in the master instance. 在数据库还原或附加到从未创建过登录名的 SQL ServerSQL Server 其他实例之后,数据库用户也可能变为孤立用户。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. 如果删除了对应的 SQL ServerSQL Server 登录名,则数据库用户可能会变为孤立用户。A database user can also become orphaned if the corresponding SQL ServerSQL Server login is dropped. 即使重新创建该登录名,它也会具有不同的 SID,因此该数据库用户仍为孤立用户。Even if the login is recreated, it will have a different SID, so the database user will still be orphaned.

检测孤立用户To Detect Orphaned Users

对于 SQL ServerSQL Server 和 PDWFor SQL ServerSQL Server and PDW

要在基于缺少 SQL ServerSQL Server 身份验证登录名的 SQL ServerSQL Server 中检测孤立用户,请在用户数据库中执行以下语句: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';  

输出中列出了当前数据库中未链接到任何 SQL ServerSQL Server 登录名的 SQL ServerSQL Server 身份验证用户以及对应的安全标识符 (SID)。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.

对于 SQL Database 和 SQL 数据仓库For SQL Database and SQL Data Warehouse

sys.server_principals 表不适用于 SQL Database 或 SQL 数据仓库。The sys.server_principals table is not available in SQL Database or SQL Data Warehouse. 在这些环境中按以下步骤识别孤立用户:Identify orphaned users in those environments with the following steps:

  1. 连接到 master 数据库,按下列查询为登录名选择 SID: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. 连接到用户数据库,使用以下查询以查看 sys.database_principals 表中用户的 SID: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. 比较两个列表,以确定用户数据库 sys.database_principals 表中是否存在与 master 数据库 sql_logins 表中不匹配的登录名 SID。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.

解决孤立用户问题To Resolve an Orphaned User

在 master 数据库中,使用带有 SID 选项的 CREATE LOGIN 语句以重新创建缺失的登录名,提供上一部分中获得的数据库用户的 SIDIn 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>;  

若要将孤立的用户映射到 master数据库中已存在的登录名,请在用户数据库中执行 ALTER USER 语句,并指定登录名。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>;  

在重新创建缺失的登录名时,用户可以使用提供的密码访问数据库。When you recreate a missing login, the user can access the database using the password provided. 然后,用户可以使用 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>';  

重要

任何登录名都可更改自己的密码。Any login can change it's own password. 只有具有 ALTER ANY LOGIN 权限的登录名才能更改其他用户的登录密码。Only logins with the ALTER ANY LOGIN permission can change the password of another user's login. 但是,只有 sysadmin 角色的成员才能修改 sysadmin 角色成员的密码。However, only members of the sysadmin role can modify passwords of sysadmin role members.

不推荐使用的过程 sp_change_users_login 也适用于孤立用户。The deprecated procedure sp_change_users_login also works with orphaned users. sp_change_users_login 不能与 SQL DatabaseSQL Database配合使用。sp_change_users_login cannot be used with SQL DatabaseSQL Database.

另请参阅See 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)