Mirror--镜像用户同步

 
--=========================================
--在镜像搭建后,在主库服务器上创建登录,并在数据库上建立对应用户,
--数据库中用户被同步到镜像数据库中,但登录是实例级对象,无法同步,
--因此需要手动同步登录到镜像实例上。
 
--当登录未同步到镜像实例上时，如果镜像发生故障转移，则应用程序
--无法访问镜像数据库，镜像数据库上未与登录向管理的用户被称为
--孤立用户
 
--=========================================
--http://msdn.microsoft.com/zh-cn/library/ms174378.aspx
 
sp_change_users_login [ @Action = ] 'action'
    [ , [ @UserNamePattern = ] 'user' ]
    [ , [ @LoginName = ] 'login' ]
    [ , [ @Password = ] 'password' ]
[;]
--Auto_Fix
--将当前数据库的sys.database_principals 系统目录视图中的用户项链接到同名的SQL Server 登录名。如果不存在同名的登录名，将会创建一个。检查Auto_Fix 语句的结果，确认实际链接是否正确。在对安全性较为敏感的情况下，要避免使用Auto_Fix。
 
--如果使用Auto_Fix 时登录名尚不存在，则必须指定user 和password，否则必须指定user，但password 将被忽略。login 必须为NULL。user 必须是当前数据库中的有效用户。不能将另一个用户映射到该登录名。
 
--Report
--列出当前数据库中未链接到任何登录名的用户以及相应的安全标识符(SID)。user、login 和password 必须为NULL 或不指定。
 
--若要使用系统表通过某个查询替换报表选项，请将sys.server_prinicpals 中的条目与sys.database_principals 中的条目进行比较。
 
--Update_One
--将当前数据库中指定的user 链接到现有的SQL Server login。必须指定user 和login。password 必须为NULL 或不指定。
 
 
--=========================================
--镜像切换后
--查看孤立用户
USE REPDB
GO
EXEC sp_change_users_login
@Action = 'REPORT',
@UserNamePattern = NULL,
@LoginName  = NULL,
@Password =  NULL
 
 
--=========================================
--创建登录[T1],并将T1和数据库[REPDB]中的用户[T1]关联
--该方式同样导致镜像两段的登录SID不同,从而导致孤立用户
USE [master]
GO
CREATE LOGIN [T1] WITH PASSWORD=N'T1', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE REPDB
GO
EXEC sp_change_users_login
@Action = 'Update_One',
@UserNamePattern = 'T1',
@LoginName  = 'T1'
 
--=========================================
--使用AUTO_FIX来解决
--该方式同样导致镜像两段的登录SID不同,从而导致孤立用户
USE REPDB
GO
EXEC sp_change_users_login
@Action = 'Auto_Fix',
@UserNamePattern = 'T2',
@Password =  'T2'
 
--=========================================
--推荐做法
--在主库上查询得到需要同步的用户sid
USE master;
select sid,name from syslogins;
 
    --============================================
--在从库上创建登录
exec sp_addlogin
@loginame = 'DB1Login',
@passwd = 'Sql@123',
@sid=0x82873B5AFFEFE54EB4F9CA05C303C9AE
 
--============================================
--MSDN: http://technet.microsoft.com/zh-cn/library/ms173768.aspx
EXEC sp_addlogin [ @loginame = ] 'login'
    [ , [ @passwd = ] 'password' ]
    [ , [ @defdb = ] 'database' ]
    [ , [ @deflanguage = ] 'language' ]
    [ , [ @sid = ] sid ]
    [ , [ @encryptopt = ] 'encryption_option' ]
[;]