通过

孤立用户疑难解答 (SQL Server)

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例Azure Synapse AnalyticsAnalytics Platform System (PDW)

当数据库用户基于数据库中的登录名，但该登录名不再存在于 SQL Server 中mastermaster时，用户将被孤立。 当删除登录名或数据库移动到登录名不存在的另一台服务器上时，可能会发生这种情况。 本文介绍如何查找孤立用户并将其重新映射到登录名。

注意

为可能被移动的数据库使用包含的数据库用户可以减少形成孤立用户的可能性。 有关详细信息，请参阅使用包含的数据库实现数据库可移植。

背景

若要连接到基于登录名的安全主体（数据库用户标识）的 SQL Server 实例上的数据库，主体必须在数据库中具有有效的登录名 master 。 此登录名用于验证主体标识的身份验证过程，并确定是否允许主体连接到 SQL Server 实例。 可在 SQL Server sys.server_principals 目录视图和 sys.sql_logins 兼容性视图中查看服务器实例上的 登录名。

SQL Server 登录名以映射到 SQL Server 登录名的“数据库用户”身份访问各个数据库。 此规则有三种例外情况：

  • 包含的数据库用户

    包含的数据库用户在用户数据库级别进行身份验证，并且与登录名无关。 建议使用此模型，因为数据库更易于移植，并且包含的数据库用户无法成为孤立数据库。 但是，必须为每个数据库重新创建它们。 此模型在具有许多数据库的环境中可能不切实际。

  • 来宾帐户

    在数据库中启用时，此帐户允许未映射到数据库用户的 SQL Server 登录名以 来宾 用户身份访问数据库。 默认情况下， guest 帐户是禁用的。

  • Microsoft Windows 组成员身份

    如果 Windows 用户是也是数据库中用户的 Windows 组的成员，则从 Windows 用户创建的 SQL Server 登录名可以访问数据库。

有关将 SQL Server 登录名映射到数据库用户的信息存储在数据库中。 它包括数据库用户的名称以及相应 SQL Server 登录名（）的安全标识符（SID）。 该数据库用户的权限用于在数据库中进行授权。

数据库用户（基于登录名），其对应的 SQL Server 登录名未定义或错误地在服务器实例上无法登录该实例。 这样的用户被称为此服务器实例上的数据库的“孤立用户” 。 如果将数据库用户映射到数据库中不存在master的登录名SID，则会发生孤立。 在数据库还原或附加到从未创建过登录名的 SQL Server 其他实例之后，数据库用户也可能变为孤立用户。 如果删除了对应的 SQL Server 登录名，则数据库用户可能会变为孤立用户。 即使重新创建登录名，它也会有其他 SID登录名，因此数据库用户仍将是孤立的。

检测孤立用户

对于 SQL Server 和 PDW

若要根据缺少的 SQL Server 身份验证登录名检测 SQL Server 中的孤立用户，请在用户数据库中运行以下语句：

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';

输出列出了当前数据库中未链接到任何 SQL Server 登录名的 SQL Server 身份验证用户和相应的 SID。

对于 Azure SQL 数据库和 Azure Synapse Analytics

sys.server_principals表在 SQL 数据库或 Azure Synapse Analytics 中不可用。 通过完成以下步骤识别这些环境中的孤立用户：

  1. 使用以下查询连接到 master 数据库并选择登录名的 SID：

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

  2. 使用以下查询连接到用户数据库并查看表中用户的 sys.database_principals SID：

    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表中是否有用户 SID 与数据库sql_logins表中的登录 SID master 不匹配。

解决孤立用户

在 master 数据库中，使用 CREATE LOGIN 语句和 SID 选项重新创建缺失的登录名。 SID提供在上一部分获取的数据库用户。

CREATE LOGIN <login_name>
WITH PASSWORD = '<use_a_strong_password_here>',
SID = <SID>;

若要将孤立用户映射到已存在的 master登录名，请在用户数据库中运行 ALTER USER 语句，并指定登录名：

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

重新创建缺少的登录名时，用户可以使用提供的密码访问数据库。 然后，用户可以使用该语句更改登录帐户 ALTER LOGIN 的密码：

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

重要

任何登录名都可更改其自己的密码。 只有具有 ALTER ANY LOGIN 权限的登录名才能更改其他用户的登录密码。 但是，只有 sysadmin 角色的成员才能修改 sysadmin 角色成员的密码。

相关内容