在数据库安全体系中,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
三,来宾用户(guest)
登录(Login)用于用户身份验证,而数据库用户(User)用于数据库的访问和权限验证。登录(Login)通过安全识别符 (SID) 与用户(User)关联。如果数据库中不存在针对特定登录(Login)的用户(User),使用该登录(Login)的用户即使能够连接到 SQL Server 服务器,也无法访问数据库。但是,该情形的唯一例外是当数据库包含“guest”用户(User)时,与用户(User)不关联的登录(Login)将被映射到 guest 用户。如果存在数据库用户(User),但没有与其关联的登录(Login),则该用户将无法登录到 SQL Server 服务器中。
四,创建孤立用户
通过Create User 命令创建User和Login之间的映射关系,上文提到,这种映射关系是通过SID来关联的,即Login访问数据库使用的User的SID和Login相同。
在创建新的User时,指定关联的Login,就可以创建User和Login的映射关系,脚本如下:
CREATE USER user_name --FOR LOGIN login_name [ WITH DEFAULT_SCHEMA = schema_name ]
在创建User时,如果没有指定for login子句,那么新建的User是孤立用户,一般用作系统维护等特殊用途。
参考文档:
Fixing orphaned database users in 2005 to 2012 – T-SQL Tuesday #025
Do you still use sp_change_users_login instead of ALTER USER UserName WITH LOGIN = UserName