如果在ALWAYSON集群中出现这样的问题:
1.同一个用户名,可以登录主节点查询某个库,在从节点却无相关查询权限,连接从节点中的某个数据库即报错。但是在该报错数据库中能查询到当前用户的权限足够
2.数据库迁移后,原正常工作的DBLINK无法正常工作,确认密码权限等未作变动,且在目标数据库中能查询到当前用户信息,而且权限足够
…
遇到上述问题,我们首先应该想到的就是孤立用户问题。
因为主节点是可以正常查询的,所以孤立用户是出现在从节点的。
我们运行一下语句可以查询到从库上的孤立用户
--检查数据库中孤立用户
exec sp_change_users_login @action='Report'
查询到孤立用户后,我们运行下列语句来进行关联孤立用户
exec sp_change_users_login
@action='update_one',
@usernamepattern='test_ro', --数据库孤立用户
@loginname='test_ro'; --关联到sql server登录名
数据库报错:数据库为只读,无法进行相关操作。
只读库不允许除查询以外的其他操作,所以正常情况下,只能在主库做关联登录名操作,然后由数据库将此操作同步至从库。
想到在主节点进行关联用户名操作,该操作会被同步至从节点,一样可以关联。于是在主节点执行相关查询,再到从节点查询,发现该孤立用户仍存在。
执行了关联操作,该登录名却未被关联,此时就猜测该登录名可能有问题。
仔细对比主库与从库该登录名信息,发现两个登录名的SID不一样,就猜测可能由这个不一致引起的。事实说明,一般这种情况都是由SID不一致引起的。
若发现SID不一致,则需要删除从库的登录名,使用主库登录名对应的SID重新创建后,在主库执行关联登录名操作。
--主库执行,查询SID
select * from sys.sql_logins
--从库执行
--删除登录名
drop login test_ro
--创建相同SID的登录名
CREATE LOGIN [test_ro] WITH PASSWORD=N'123456',
sid = 0x3528851918AD98438D05C47644D4A21B ,
-- sid 与主副本库上对应的登录名的sid相同
DEFAULT_DATABASE=[master]
--创建成功后再执行一次关联用户名操作
exec sp_change_users_login
@action='update_one',
@usernamepattern='test_ro', --数据库孤立用户
@loginname='test_ro'; --关联到sql server登录名
此时再查询数据库,会发现该孤立用户消失,该用户在从节点已可以正常查询。