求助：alwayson 只读副本权限控制问题解决思路征集

[山寨DBA]

环境：Windows server 2012 + sqlserver 2012 R2
功能：alwayson 实现读写分离 + 自动故障转移
问题（目前测试环境我已经折腾出来了，但是反复测试过程中发现如下坑爹的问题）：即，可读副本权限控制问题。因为alwayson是针对于库级别的高可用性组，而且只读副本是“只读”的，导致alwayson搭好之后无法添加账号到只读副本上的各个库，所以故障切换之后主库（故障转移之前是只读辅库）没有原来主库的登录名等权限。
我这边目前的方案有两个，但是都非常不理想：
方案1：
主库写库正常read/write权限，并且准备一份主库写库程序访问的账号create的 脚本，随时在故障转移的时候执行（工作量较大，而且非常不灵活，人工干预过多，容易出错），同时 赋予程序访问只读辅库的账号sysadmin权限（这个凶险程度可想而知） 。
方案2：
辅助库+主库账号 全部授予sysadmin权限（凶险程度更上一层，但是相对而言管理简单，而且切换非常灵活，几乎不需要人工干预）

诸位大神对alwayson有深层研究的，有什么好的建议，请不吝赐教。

灰常感谢。。。

[卖水果的net]

没研究过 Always on ，只是看一些资料，没动手安装过，关注本帖子。

[卖水果的net]

没研究过 Always on ，只是看一些资料，没配置过，关注本帖子。

[专注or全面]

http://blog.csdn.net/burgess_liu/article/details/11904921

[山寨DBA]

引用 3 楼 x_wy46 的回复:

http://blog.csdn.net/burgess_liu/article/details/11904921

谢兄台赐教。 此方法我已经测试过了，但是存在如下问题：每次切换都需要去手动执行这个脚本创建一遍登录名（而且事先还要删掉辅助副本上的登录名），虽然也能解燃眉之急，这个没法提供自动故障切换呢（因为切换之后如果不执行这些脚本，权限还是过不去的）。 不晓得可还有更风骚一些的方法？

[Q315054403]

你应该看下 Contain Database，中文译为包含数据库，意思是说将登录建立在数据库上，而不是Master里

[山寨DBA]

引用 5 楼 luckyrandom 的回复:

你应该看下 Contain Database，中文译为包含数据库，意思是说将登录建立在数据库上，而不是Master里

兄台何意 ？可否列举一两个例子参考一下？ 在下愚钝，请明示。

[prc215]

引用 4 楼 hwhmh2010 的回复:

[quote=引用 3 楼 x_wy46 的回复:] http://blog.csdn.net/burgess_liu/article/details/11904921

谢兄台赐教。 此方法我已经测试过了，但是存在如下问题：每次切换都需要去手动执行这个脚本创建一遍登录名（而且事先还要删掉辅助副本上的登录名），虽然也能解燃眉之急，这个没法提供自动故障切换呢（因为切换之后如果不执行这些脚本，权限还是过不去的）。 不晓得可还有更风骚一些的方法？[/quote] 不很简单吗？按照例子，事先建好这些登陆就可以了

[cs_lb]

引用 7 楼 prc215 的回复:

[quote=引用 4 楼 hwhmh2010 的回复:] [quote=引用 3 楼 x_wy46 的回复:] http://blog.csdn.net/burgess_liu/article/details/11904921

谢兄台赐教。 此方法我已经测试过了，但是存在如下问题：每次切换都需要去手动执行这个脚本创建一遍登录名（而且事先还要删掉辅助副本上的登录名），虽然也能解燃眉之急，这个没法提供自动故障切换呢（因为切换之后如果不执行这些脚本，权限还是过不去的）。 不晓得可还有更风骚一些的方法？[/quote] 不很简单吗？按照例子，事先建好这些登陆就可以了[/quote] SID要一样。

[山寨DBA]

引用 9 楼 cs_lb 的回复:

[quote=引用 7 楼 prc215 的回复:] [quote=引用 4 楼 hwhmh2010 的回复:] [quote=引用 3 楼 x_wy46 的回复:] http://blog.csdn.net/burgess_liu/article/details/11904921

谢兄台赐教。 此方法我已经测试过了，但是存在如下问题：每次切换都需要去手动执行这个脚本创建一遍登录名（而且事先还要删掉辅助副本上的登录名），虽然也能解燃眉之急，这个没法提供自动故障切换呢（因为切换之后如果不执行这些脚本，权限还是过不去的）。 不晓得可还有更风骚一些的方法？[/quote] 不很简单吗？按照例子，事先建好这些登陆就可以了[/quote] SID要一样。[/quote] SID当然是一样的喽，直接从主副本用脚本生成的，复制到辅助副本执行的。

[山寨DBA]

引用 7 楼 prc215 的回复:

[quote=引用 4 楼 hwhmh2010 的回复:] [quote=引用 3 楼 x_wy46 的回复:] http://blog.csdn.net/burgess_liu/article/details/11904921

谢兄台赐教。 此方法我已经测试过了，但是存在如下问题：每次切换都需要去手动执行这个脚本创建一遍登录名（而且事先还要删掉辅助副本上的登录名），虽然也能解燃眉之急，这个没法提供自动故障切换呢（因为切换之后如果不执行这些脚本，权限还是过不去的）。 不晓得可还有更风骚一些的方法？[/quote] 不很简单吗？按照例子，事先建好这些登陆就可以了[/quote] 看上去确实简单，但是实际测试证明：切换之后登录名和配置就失效了，所有读写压力届时会集中到主副本上。需要重新执行3楼兄台给的脚本，创建登录名。

[飞啊子]

引用 10 楼 hwhmh2010 的回复:

[quote=引用 9 楼 cs_lb 的回复:] [quote=引用 7 楼 prc215 的回复:] [quote=引用 4 楼 hwhmh2010 的回复:] [quote=引用 3 楼 x_wy46 的回复:] http://blog.csdn.net/burgess_liu/article/details/11904921

谢兄台赐教。 此方法我已经测试过了，但是存在如下问题：每次切换都需要去手动执行这个脚本创建一遍登录名（而且事先还要删掉辅助副本上的登录名），虽然也能解燃眉之急，这个没法提供自动故障切换呢（因为切换之后如果不执行这些脚本，权限还是过不去的）。 不晓得可还有更风骚一些的方法？[/quote] 不很简单吗？按照例子，事先建好这些登陆就可以了[/quote] SID要一样。[/quote] SID当然是一样的喽，直接从主副本用脚本生成的，复制到辅助副本执行的。[/quote] 你的SID肯定不一样。如果一样，不存在你说的问题的。

-- 查sid语句。请确认N个节点服务器的sid是一样的.
SELECT * FROM sys.syslogins s WHERE s.name='用户名'

另，"直接从主副本用脚本生成的，复制到辅助副本执行的" 这步你是怎么操作的。 这种生成，是需要网上找脚本在master库执行，然后生成 创建用户脚本，sql server本身没有这样的功能。

[飞啊子]

 --生成的用户脚本，应该类似这样的。
CREATE LOGIN [xxxx] WITH 
PASSWORD = 0x0100EB86CD4672A26703F48A59BA241BBC157A48688166630190 HASHED
, SID = 0xECBC1BCBDEC838418B9F535132DCC1F2
, DEFAULT_DATABASE = [master]
, CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
 go

[山寨DBA]

引用 12 楼 feiazifeiazi 的回复:

 --生成的用户脚本，应该类似这样的。
CREATE LOGIN [xxxx] WITH 
PASSWORD = 0x0100EB86CD4672A26703F48A59BA241BBC157A48688166630190 HASHED
, SID = 0xECBC1BCBDEC838418B9F535132DCC1F2
, DEFAULT_DATABASE = [master]
, CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
 go

你说的我都晓得。。。这些问题都搞定了，一切配置好之后都是可以用的，读写也能分离。 我的问题是：测试下来发现，如果发生故障转移，那些个脚本都要重新执行，权限等问题要重新加