SQLServer 数据库还原中的孤立用户问题

--***********************************************************
/*
下面的代码演示了孤立用户的产生过程。在代码中，产生创建了一个测试数据库，
并且创建了两个登入aa和bb，bb拥有测试数据库中的对象，aa不拥有测试数据库中的对象，
建立好测试数据库并且备份后，从服务器中删除测试数据库和登入，用以模拟没有事先创建
登入的目标服务器，最后还原测试数据库的备份，演示孤立用户的产生过程。

*/
--创建一个测试数据库
create database tt
go


--创建两个登入
create login aa with password='12345q_1', DEFAULT_DATABASE =tt
go
create login bb with password='12345q_2', DEFAULT_DATABASE =tt
go


--设置登入的默认数据库为测试数据库tt


--授予登入访问数据库权限


use tt


create USER user1 FOR LOGIN aa 
create USER user2 FOR LOGIN bb 
CREATE SCHEMA scm_b AUTHORIZATION user2


--创建一个属于用户bb的表
create table scm_b.tb(id int)
go




--至此，测试环境已经创建好了，使用用户aa或者bb登入，验证用户的可用性


--用户验证完成后，备份并删除测试数据库，演示孤立用户的产生过程
use master
go
backup database tt to disk = 'd:\tt.bak' with format


drop database tt
go


--删除登入，用以模拟目标服务器没有事先创建登入时的情况
DROP LOGIN  aa
go
DROP LOGIN  bb
go


--还原测试数据库
restore database tt from disk = 'd:\tt.bak'
go


--察看还原后的测试数据库用户
use tt
go
sp_helpuser
go


--可以看到用户aa,bb在还原后的数据库中确实存在，但使用aa或者bb登入时，会提示登入失败


--重新创建两个登入
use master
go
create login aa with password='12345q_1', DEFAULT_DATABASE =tt
go
create login bb with password='12345q_2', DEFAULT_DATABASE =tt
go


--授予登入访问数据库权限
use db
go
CREATE USER user1 FOR LOGIN aa 
CREATE USER user2 FOR LOGIN bb 
/*
消息 15023，级别 16，状态 1，第 1 行
用户、组或角色 'user1' 在当前数据库中已存在。
消息 15023，级别 16，状态 1，第 1 行
用户、组或角色 'user2' 在当前数据库中已存在。
*/


--尝试删除测试数据库中的用户
DROP user user1


DROP user user2
go
/*
消息 15138，级别 16，状态 1，第 1 行
数据库主体在该数据库中拥有 架构，无法删除。
*/




--再次授予aa登入访问数据库权限
use tt
go
create login aa with password='12345q_1', DEFAULT_DATABASE =tt
go


/*
通过演示，可以知道，对于不拥有数据库中对象的用户，可以直接删除数据库中的孤立用户，
然后重新授予登入访问数据库的权限即可
对于拥有数据库对象的孤立用户，除非事先先除去该用户拥有的对象，否则无法删除。
可以通过系统存储过程sp_change_users_login来解决。sp_change_users_login，将数据库中现有的用户
映射到sql server登入


语法
sp_change_users_login [ @Action = ] 'action'
    [ , [ @UserNamePattern = ] 'user' ]
    [ , [ @loginName = ] 'login' ]


参数
[ @Action = ] 'action'


描述此过程要执行的操作。action 的数据类型为 varchar(10)，可以是下面的某个值。


值 描述 
Auto_Fix 将当前数据库的 sysusers 表中的用户条目链接到 syslogins 中同名的登录上。 
Report 列出当前数据库中未链接到任何登录的用户及其对应的安全标识号 (SID)。  
Update_One 将当前数据库中指定的 user 链接到 login。login 必须已经存在。必须指定 user 和 login。 




[@UserNamePattern =] 'user'


是当前数据库中的 SQL Server 用户的名称。user 的数据类型为 sysname，默认值为 NULL。sp_change_users_login 只能用于 SQL Server 登录和用户的安全帐户；而不能用于 Microsoft Windows NT? 用户。


[@LoginName =] 'login'


SQL Server 登录的名称。login 的数据类型为 sysname，默认值为 NULL。




*/


例如：对于孤立用户示例，在恢复数据库后，可以使用下面代码列出恢复的数据库中的孤立用户
sp_change_users_login 'report'


使用下列代码解决测试数据库中产生孤立用户bb，如果事先未建立登入bb，则sp_change_users_login自动
创建一个名为bb的登入，登入密码为bb，如果已经建立事先登入bb，则仅将测试数据库中的用户bb与sql server实例中的
登入建立联结


use tt
exec sp_change_users_login 'Update_One','user2','bb'