184.数据库还原中的孤立用户处理案例

以你之姓@ 2024-01-20 07:00 33阅读 0赞

--创建一个测试的数据库
    CREATE DATABASE db
    GO
    
    --创建两个登录
    EXEC sp_addlogin 'aa'
    EXEC sp_addlogin 'bb'
    
    --设置登录的默认数据库为测试数据库 db
    EXEC sp_defaultdb 'aa','db'
    EXEC sp_defaultdb 'bb','db'
    GO
    
    --授予登录访问数据库的权限(将登录设置为数据库的用户)
    USE db
    EXEC sp_grantdbaccess 'aa'
    EXEC sp_grantdbaccess 'bb'
    
    --创建一个属于用户bb的表
    CREATE TABLE bb.tb(id int)
    GO
    
    --至此,测试环境已经创建好了,使用用户aa或者bb从查询分析器登录,验证用户的可用性
    
    --用户验证完成后,备份并删除测试数据库,演示孤立用户的产生过程
    USE master
    BACKUP DATABASE db TO DISK='c:\db.bak' WITH FORMAT
    DROP DATABASE db
    GO
    
    --删除登录,用以模拟目标服务器没有事先创建登录时的情况
    EXEC sp_droplogin 'aa'
    EXEC sp_droplogin 'bb'
    GO
    
    --还原测试数据库
    RESTORE DATABASE db FROM DISK='c:\DB.bak'
    GO
    
    --查看还原后的测试数据库的用户
    USE db
    EXEC sp_helpuser
    /*--结果
    UserName GroupName   LoginName DefDBName UserID SID
    -------- --------- -------- -------- ----- --------------------------------------------
    aa   public    NULL   NULL   5  0x71BA40BA3C89FA43A5DFD74620E81FDA
    bb   public    NULL   NULL   6  0xDE41BBF527C4F545AFFA0EBAE864DE40
    dbo  db_owner  NULL   NULL   1  0x010500000000000515000000BE604AE3EA4BA8F3E9CE046BF4010000
    --*/
    
    --可以看到用户aa,bb在还原后的数据库中确实存在,但使用aa或者bb登录时,会提示"用户'aa'登录失败"
    GO
    
    --下面重新建立登录aa,bb
    EXEC sp_addlogin 'aa'
    EXEC sp_addlogin 'bb'
    
    --这次可以使用aa,bb登录,但登录后无法访问测试数据库db
    GO
    
    --将登录设置为测试数据库的用户
    USE db
    EXEC sp_grantdbaccess 'aa'
    EXEC sp_grantdbaccess 'bb'
    /*--将收到错误信息
    服务器: 消息 15023，级别 16，状态 1，过程 sp_grantdbaccess，行 147
    当前数据库中已存在用户或角色 'aa'。
    服务器: 消息 15023，级别 16，状态 1，过程 sp_grantdbaccess，行 147
    当前数据库中已存在用户或角色 'bb'。
    --*/
    GO
    
    --尝试删除测试数据库中的用户
    EXEC sp_revokedbaccess 'aa'
    /* 用户aa可以正常删除,因为用户aa在测试数据库中没有对象 */
    EXEC sp_revokedbaccess 'bb'
    /*--将收到错误信息
    服务器: 消息 15183，级别 16，状态 1，过程 sp_MScheck_uid_owns_anything，行 17
    该用户拥有数据库中的对象，无法除去该用户。
    name        type
    ----------- -------
    tb          U 
    --*/
    
    --再次授予登录aa访问测试数据库的权限
    EXEC sp_grantdbaccess 'aa'
    
    --这次可以顺利授权,并且用aa登录也可以正常访问测试数据库.
    
    
    /*=================================================================*/
    --解决孤立用户bb
    
    --1. 报告孤立用户
    USE db
    EXEC sp_change_users_login 'Report'
    GO
    
    
    --2. 处理孤立用户
    USE db
    EXEC sp_change_users_login 'Auto_Fix','bb',null,'bb'