SQL Server 孤立用户检测与处理

本文详细介绍了SQL Server中孤立用户的概念、检测方法及处理策略。包括利用系统存储过程和SQL查询语句来查找孤立用户,以及使用sp_change_users_login修复孤立用户,或在不再需要时直接删除孤立用户。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

SQL Server 登录名使用映射到 SQL Server 登录名的数据库用户访问各个数据库。此规则有两种例外情况:

  • guest 帐户。

    这个帐户在数据库中启用后,能够使未映射到数据库用户的 SQL Server 登录名作为 guest 用户进入数据库。

  • Microsoft Windows 组成员身份。

    如果某 Windows 用户是 Windows 组的成员,并且此组也是数据库中的用户,则基于该 Windows 用户创建的 SQL Server 登录名可以进入数据库。

有关 SQL Server 登录名与数据库用户的映射关系的信息存储在数据库中。其中包括数据库用户的名称以及对应 SQL Server 登录名的 SID。该数据库用户的权限用于在数据库中进行授权。

在服务器实例上未定义或错误定义了其相应 SQL Server 登录名的数据库用户无法登录到实例。这样的用户被称为此服务器实例上的数据库的“孤立用户”。如果删除了对应的 SQL Server 登录名,则数据库用户可能会变为孤立用户。另外,在数据库还原或附加到 SQL Server 的其他实例之后,数据库用户也可能变为孤立用户。如果未在新服务器实例中提供数据库用户映射到的 SID,则该用户可能变为孤立用户。


简单的说孤立用户,就是数据库中有这个用户,但是没有对应的登录用户


检测孤立用户

USE <database_name>;
GO; 
sp_change_users_login @Action='Report';
GO;

这样找到的会不包含windows 孤立账号,所以是不完整的,根据孤立用户的特性可以根据以下语句找到所有的

SELECT name
FROM sys.database_principals DP WITH(NOLOCK) 
WHERE NOT EXISTS( 
                SELECT * 
                FROM sys.server_principals SP WITH(NOLOCK) 
                WHERE SP.sid = DP.sid 
        ) 
        AND DP.type IN( 
                'S', 'G', 'U' 
        ) 
        AND DP.sid IS NOT NULL 
        AND DP.sid <> 0x00 


处理孤立账号

1、可以使用 sp_change_users_login 修复

 以下示例显示如何使用Auto_Fix将现有用户映射到同名的登录名,以及如何在不存在登录名Mary的情况下,创建密码为B3r12-3x$098f6的 SQL Server 登录名Mary。
SE AdventureWorks;
GO
EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-3x$098f6';
GO

需要注意的是
使用 sp_change_users_login 将当前数据库中的数据库用户链接到 SQL Server 登录名。如果用户登录名已更改,则使用 sp_change_users_login 将用户链接到新的登录,而不会丢失用户的权限。新的 login 不能为 sa,而 user 不能为 dbo、guest 或 INFORMATION_SCHEMA 用户。

sp_change_users_login 不能用于将数据库用户映射到 Windows 级主体、证书或非对称密钥。

sp_change_users_login 不能与通过 Windows 主体创建的 SQL Server 登录名一起使用,也不能与使用 CREATE USER WITHOUT LOGIN 创建的用户一起使用。

不能在用户定义的事务中执行 sp_change_users_login。

2、 如果这些账号没有用了,可以考虑直接删除,附上一个快速删除的代码

删除当前库的所有孤立用户

declare @s varchar(1000)
if OBJECT_ID('tempdb..#tb') is not null drop table #tb
create table #tb(username varchar(50))
insert into #tb
SELECT name
FROM sys.database_principals DP WITH(NOLOCK) 
WHERE NOT EXISTS( 
                SELECT * 
                FROM sys.server_principals SP WITH(NOLOCK) 
                WHERE SP.sid = DP.sid 
        ) 
        AND DP.type IN( 
                'S', 'G', 'U' 
        ) 
        AND DP.sid IS NOT NULL 
        AND DP.sid <> 0x00 
if @@ROWCOUNT >0
begin
select @s=ISNULL(@s,'')+'drop user ['+username+'];'+CHAR(13)from #tb
--print @s
exec (@s)
end     
drop table #tb



删除所有数据库的孤立用户

exec sp_msforeachdb
'use [?]
declare @s varchar(1000)
if OBJECT_ID(''tempdb..#tb'') is not null drop table #tb
create table #tb(username varchar(50))
insert into #tb SELECT name
FROM sys.database_principals DP WITH(NOLOCK) 
WHERE NOT EXISTS( 
                SELECT * 
                FROM sys.server_principals SP WITH(NOLOCK) 
                WHERE SP.sid = DP.sid 
        ) 
        AND DP.type IN( 
                ''S'', ''G'', ''U'' 
        ) 
        AND DP.sid IS NOT NULL 
        AND DP.sid <> 0x00 ;
if @@ROWCOUNT >0
begin
select @s=ISNULL(@s,'''')+''drop user [''+username+''];''+CHAR(13)from #tb
use [?]
--print  @s
exec (@s)
end
drop  table #tb
'

<think>我们正在处理SQL Server错误15404,错误代码0x534,该错误通常表示SQL Server无法获取Windows NT组/用户的信息。根据引用[2]中的信息,这个错误可能是由于计算机主机名更改导致的,因为SQL Server在安装时绑定的实例名是原来的主机名,更改后导致无法识别当前用户。此外,引用[1]和引用[3]提到了一些权限和清理残留相关的解决方案。 解决方案思路: 1. 确认当前计算机主机名SQL Server实例名是否匹配。如果不匹配,可能需要更新SQL Server的配置或重新设置关联。 2. 检查SQL Server服务账户的权限,确保其有足够的权限访问所需资源。 3. 如果之前安装失败有残留,清理注册表和文件残留。 具体步骤: 步骤1:检查计算机主机名和SQL Server实例名 - 查看当前计算机主机名:右键点击“此电脑”->“属性”,查看“设备名称”(或通过命令提示符输入`hostname`命令查看)。 - 打开SQL Server配置管理器,查看SQL Server服务的实例名。默认实例名为MSSQLSERVER,命名实例则会有特定名称。 - 如果计算机主机名已更改,而SQL Server服务仍然使用旧的主机名,则可能导致问题。 步骤2:修改SQL Server登录账户 - 打开SQL Server Management Studio (SSMS),使用Windows身份验证登录。 - 在“安全性”->“登录名”中,找到当前出错的登录账户(错误信息中提到的账户,如DESKTOP-2U25RBA\Lenovo)。 - 如果该账户已不存在(因为主机名更改),则需要删除旧的登录账户,并添加新的登录账户(使用新的主机名或域名)。 - 或者,尝试修改现有登录账户的SID(安全标识符)以匹配新的主机名,但通常更简单的方法是删除并重新创建。 步骤3:检查服务账户权限 - 打开“服务”(services.msc),找到SQL Server服务(如SQL Server (MSSQLSERVER))。 - 右键点击属性,在“登录”选项卡中查看服务使用的账户。通常为“NT SERVICE\MSSQLSERVER”(默认实例)或“NT SERVICE\<实例名>”。 - 确保该账户在本地安全策略中有足够的权限(例如,作为服务登录的权限)。可以通过“本地安全策略”(secpol.msc)->“本地策略”->“用户权限分配”->“作为服务登录”中添加该账户。 - 引用[1]中提到手动创建NT SERVICE\MSSQLSERVER账户权限,实际上该账户是虚拟账户,通常不需要手动创建,但需要确保其有权限。 步骤4:清理残留(如果之前安装失败) - 如果之前安装失败,按照引用[3]的方法,卸载所有SQL Server组件,并删除注册表项(备份后删除): HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer - 同时,删除SQL Server的安装目录(通常为C:\Program Files\Microsoft SQL Server)和程序数据目录(C:\ProgramData\Microsoft SQL Server)。注意:删除前备份重要数据库文件。 步骤5:重新安装SQL Server(如果以上步骤无效) - 在清理残留后,重新安装SQL Server。 注意:在操作注册表前务必备份,以免系统出现问题。 根据引用[2]中的具体案例,用户通过更改主机名后,重新设置SQL Server的登录账户解决了问题。因此,重点应放在步骤1和步骤2。 另外,错误0x534可能还SID不一致有关。我们可以尝试以下SQL命令来修复孤立用户: ```sql USE [数据库名] GO EXEC sp_change_users_login 'Auto_Fix', '用户名' GO ``` 但是,错误15404通常发生在登录阶段,而不是在数据库用户映射阶段。因此,上述命令可能不适用。主要问题在于登录名本身。 综上所述,我们给出以下解决方案: 1. 确认并修复主机名SQL Server实例的关联。 2. 更新或重新创建登录账户。 3. 检查服务账户权限。 4. 清理残留并重新安装(作为最后手段)。 请用户根据具体情况逐步尝试。</think>### SQL Server 错误 15404 (0x534) 解决方案 错误 15404 表示 SQL Server 无法获取 Windows NT 用户/组信息,通常发生在更改计算机名后,导致 SQL Server 无法识别原始主机名关联的账户权限。以下是系统化的解决方案: #### 1. **验证计算机名一致性** - 按 `Win+R` 输入 `cmd` 执行命令: ```bash hostname ``` - 打开 **SQL Server 配置管理器** > 检查实例名是否当前主机名匹配 - **问题根源**:若主机名被修改(如从 `DESKTOP-OLD` 改为 `NEW-PC`),SQL Server 仍尝试访问旧主机名关联的账户[^2] #### 2. **修复登录账户映射** ```sql USE [master] GO -- 检查孤立登录账户 SELECT name, sid FROM sys.syslogins WHERE name LIKE '%旧主机名%'; -- 删除无效登录账户 (示例) DROP LOGIN [DESKTOP-OLD\User]; GO -- 重建新主机名账户 CREATE LOGIN [NEW-PC\User] FROM WINDOWS; GO ``` **注意**:将 `DESKTOP-OLD` 替换为错误信息中的旧主机名,`NEW-PC` 替换为当前主机名[^2] #### 3. **重置服务账户权限** 1. 打开 **服务** (`services.msc`) 2. 找到 `SQL Server (MSSQLSERVER)` 服务 3. 右键 **属性** > **登录** 选项卡 4. 选择 **内置账户** > `Local System` 或指定有效域账户 5. 重启 SQL Server 服务[^1] #### 4. **清理安装残留** - 卸载所有 SQL Server 组件(控制面板 > 程序和功能) - 删除注册表项(操作前备份注册表): ``` HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer ``` - 删除安装目录: ``` C:\Program Files\Microsoft SQL Server C:\ProgramData\Microsoft SQL Server ``` - 重新安装 SQL Server[^3] #### 5. **验证用户权限** - 按 `Win+R` 输入 `secpol.msc` - 导航至 **安全设置** > **本地策略** > **用户权限分配** - 检查以下策略是否包含 SQL Server 服务账户: - `以服务身份登录` - `替换进程级令牌` - 添加 `NT SERVICE\MSSQLSERVER` 账户到策略[^1] --- ### 关键预防措施 1. **主机名规范**:安装 SQL Server 前确定最终主机名,避免后续修改 2. **服务账户隔离**:为 SQL Server 创建专用域账户,避免使用本地账户 3. **定期维护**:使用 `sp_validatelogins` 存储过程检测孤立账户 > 执行账户操作后需重启 SQL Server 服务。若问题持续,检查 Windows 事件查看器 (`eventvwr.msc`) 中 `Application` 和 `System` 日志的关联错误。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值