SQL Server 孤立用户检测与处理

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的SQLServer登录名Mary。
SEAdventureWorks;

GO

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