在SQL Server中管理孤立用户：全面指南

在SQL Server中管理孤立用户对数据库安全和管理至关重要。当一个数据库用户存在而在主数据库中没有相应的登录时，就会出现孤立用户。这种不匹配可能会影响访问并导致潜在的安全漏洞。在本文中，我们将探讨什么是孤立用户，如何检测它们以及解决它们的方法，包括更新的示例和场景。

背景

要连接到SQL Server数据库，用户必须在主数据库中具有有效的登录。此登录用于验证用户并映射到数据库用户以进行授权。该映射使用安全标识符（SID）进行维护，确保数据库用户和服务器登录正确链接。

不遵循登录-用户映射规则的例外情况包括：

1. 包含数据库用户：在数据库级别进行身份验证，消除对服务器登录的依赖。这些用户增强了数据库的可移植性，但需要为每个数据库单独重建。
2. 访客账户：默认启用，允许用户访问而无需明确映射，但基本上出于安全原因不建议使用。
3. Microsoft Windows组成员：如果将组添加为用户，Windows组成员可以访问数据库。

当：

• 对应的登录被删除时。
• 迁移或还原数据库时没有关联的登录。
• 用户与登录之间的SID映射不正确。

什么是孤立用户？

孤立用户发生在以下情况：

• 数据库用户存在，但主数据库中对应的SQL Server登录不存在。
• 数据库用户SID和登录SID不匹配。

这些情况会干扰身份验证和授权，导致错误或访问受限。

检测孤立用户

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

对于Azure SQL数据库或Synapse分析

1. 检索主数据库中登录的SIDs：

SELECT sid 

FROM sys.sql_logins 

WHERE type = 'S'; 

2. 检索目标数据库中用户的SIDs：

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. 比较列表以识别不匹配。

解决孤立用户

1. 使用SID重新创建缺失的登录 

如果登录被删除：

CREATE LOGIN <login_name>   

WITH PASSWORD = '<use_a_strong_password_here>',  

SID = <SID>;

2. 将孤立用户映射到现有登录 

如果登录存在但未映射：

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

3. 更改用户密码 

在重新创建登录后，更新其密码：

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

使用sp_FindOrphanedUser

存储过程sp_FindOrphanedUser是检测和解决孤立用户的强大工具。它提供：

1. 孤立用户的列表。
2. 自动生成的修复脚本。
3. 由孤立用户拥有的模式的详细信息。

从sp_FindOrphanedUser获取包代码。

运行存储过程

默认执行：

EXEC sp_FindOrphanedUser;

对于特定数据库：

EXEC sp_FindOrphanedUser @DatabaseName = '<DatabaseName>';

孤立用户场景示例

示例1：没有登录的用户

场景

删除登录，导致数据库用户被孤立。

• 检测孤立用户：
  MS SQL

   

  EXEC sp_FindOrphanedUser;

• MS SQL

   

  创建 登录名 [User1] 密码 = 'SecurePassword123';

  USE [ExampleDB];

  修改 用户 [User1] 的登录名 为 [User1];

示例2：SID不匹配

场景

登录SID与用户SID不匹配。

• 检测不匹配项：
  MS SQL

  执行 sp_FindOrphanedUser @DatabaseName = 'ExampleDB';

• 更正SID映射：
  MS SQL

   

  USE [ExampleDB];

  EXEC sp_change_users_login 'UPDATE_ONE', 'User2', 'User2';

示例3：拥有模式的孤立用户

情景

孤立用户阻止了模式的删除。

• 检测模式所有权：
  MS SQL

   

  EXEC sp_FindOrphanedUser @DatabaseName = 'ExampleDB';

• 重新分配架构所有权：
  MS SQL

   

  USE [ExampleDB];

  ALTER AUTHORIZATION ON SCHEMA::[User3Schema] TO [dbo];

  DROP USER [User3];

最佳实践

• 使用独立数据库用户: 避免依赖服务器登录。
• 同步登录: 在数据库迁移期间始终迁移或恢复登录。
• 定期审计: 定期检查孤立用户以维护安全性。

结论

管理孤立用户确保数据库安全和操作效率。通过使用查询和像sp_FindOrphanedUser这样的工具，您可以快速识别和解决问题，保持合法用户的无缝访问。采用诸如独立用户和定期审计等最佳实践可以防止未来出现此类问题。