SQL Server 孤立用户检测与处理

最新推荐文章于 2021-04-09 17:36:37 发布

原创最新推荐文章于 2021-04-09 17:36:37 发布 · 4.3k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#sql server #数据库 #login #windows #table #sqlserver

本文详细介绍了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;

EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-3x$098f6';

需要注意的是

使用 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
'