“孤立”用户

在数据库安全体系中，Login和User是两个最基本的安全主体（Principal），Login用于登陆到SQL Server实例，而User用于访问数据库。Login和User之间有一个映射关系，通过SID（安全标识，Security ID）连接到一起。在一个数据库中，如果一个User没有相应的Login，称作孤立用户（Orphaned User），也就是说，该User的SID存在于sys.database_principals 中， 而不存在于 sys.server_principals 中。一般情况下，把备份的数据库还原到在其它SQL Server实例之后，会产生孤儿用户。还有一种情况，孤立用户是DBA故意创建的，通过权限模拟来实现特定的系统维护任务。

一，检查和修复孤立用户

Login 和 User的映射关系是通过SID来关联的，如果一个SID 存在于sys.database_principals，而不存在于  sys.server_principals，那么这个User 除非是system user，否则就是孤立用户。出现孤立用户的根本原因是：数据库User没有对应的Login，解决孤立用户问题的方法是：创建Login，建立Login和孤立用户之间的映射。

1，使用以下脚本查看孤立用户

在查看孤立用户时，应该过滤掉系统预先创建的用户，例如，dbo、sys和guest（来宾用户），一般情况下，只查看SQL User，Windows Users和Windows Group这三个安全主体类型，通过以下脚本查看孤立用户，并修复孤立用户。大多数情况下，在把数据库备份还原到不同的SQL Server实例时，会出现孤立用户。

select dp.name as UserName
    ,dp.type
    ,dp.type_desc
    ,dp.default_schema_name
    ,dp.is_fixed_role
    ,dp.authentication_type
    ,dp.authentication_type_desc
    ,dp.sid,dp.principal_id
from sys.database_principals dp
left join sys.server_principals sp 
    on dp.sid=sp.sid
where sp.sid is null
    and dp.[type] IN (N'U', N'S',N'G')
    and dp.is_fixed_role = 0
    and dp.[Name] NOT IN (N'dbo', N'guest', N'sys', N'INFORMATION_SCHEMA')

在视图 sys.database_principals中，Principal的类型注释如下:

• S = SQL user
• U = Windows user
• G = Windows group

2，创建Windows Login

创建Windows Login，Logon Name的格式是：[<domainName>\<login_name>]

CREATE LOGIN [DomainName\WindowsLoginName] -- or [DomainName\WindowsGroupName] 
FROM WINDOWS WITH DEFAULT_DATABASE=[master], 
DEFAULT_LANGUAGE=[us_english]

3，重新创建Login 和 name之间的映射关系

通过为User指定新的Login，重新把User映射到Login。Login 和User的映射关系是通过SID（security Identifier）来关联的，在重新映射时，数据库引擎会把User的SID修改为Login的SID，以建立映射关系。

ALTER USER userName  
WITH LOGIN = loginName

注：The WITH LOGIN clause enables the remapping of a user to a different login. 

二，自动修复孤立用户问题

对于孤立用户，由于User  Name和 Login Name之间没有直接的关系，因此，完全修复孤立用户的可能性几乎是没有的。在特定的情况下，当使用Windows验证创建User和Login时，把User Name 和 Login Name设置成相同的，这样，可以检测数据库的User是否有对应的Login；如果没有对应的Login，管理员可以新建相应的Windows Login，重建映射关系，进而修复孤立用户。

以下是自动修复孤立用户的脚本，仅供参考：

declare @username sysname
declare @sqlcmd nvarchar(max)

declare cur_orphaned cursor 
    local
    forward_only
    fast_forward
    read_only
for 
select dp.name as UserName
from sys.database_principals dp
left join sys.server_principals sp 
    on dp.sid=sp.sid
where sp.sid is null
    and dp.[type] IN (N'U',N'G')
    and dp.is_fixed_role = 0
    and dp.[Name] NOT IN (N'dbo', N'guest', N'sys', N'INFORMATION_SCHEMA');

open cur_orphaned
fetch next from cur_orphaned into @username

while @@fetch_status=0
begin
    --create login
    set @sqlcmd = N'create login [' + @username + N'] from windows'
    exec(@sqlcmd)

    --remap user and login
    set @sqlcmd = N'alter user [' + @username + N'] with login = [' + @username + N']'
    exec(@sqlcmd)

    fetch next from cur_orphaned into @username
end

close cur_orphaned
deallocate cur_orphaned

View Code