always on发生故障转移后，dbdatareader固定数据库角色丢失？

Question

数据库SQL server2012 双节点组成群集

我们的数据库需要和第三方的系统对接，会给他们开通一个数据库用户reader，这个用户只有select权限；

当我组成always on架构后，我在双节点实例上都创建一个reader的登录名并在用户映射时给予它在相应数据库的dbo架构，让其包含db_datareader角色身份。

此时第三方系统可以通过reader用户正常访问我们的数据库，当发生故障转移后，会出现实例中reader登录名存在，但它映射在数据库的dbo架构丢失，db_datareader角色身份也随之无效，第三方系统只能通过reader登录我们的实例，却无法select。

请问这个问题要如何解决？

Answer 1

你好，

发生此问题的原因可能是每个服务器上SQL Server登录名的SID（安全标识符）不匹配。你需要在辅助副本服务器上创建此SQL Server登录名时，不仅要使用与主副本服务器上相同的名称，还要使用相同的SID。

在辅助副本服务器上创建登录名时，可以通过使用“ CREATE LOGIN”语句中的SID规范来实现。要从主体服务器检索每个登录名的SID，请查询sys.sql_logins目录视图。这是一个查询示例，它将为给定服务器上的每个SQL / Windows登录生成实际的“ CREATE LOGIN ...”语句：

SELECT 
  'create login [' + p.name + '] ' + 
  case when p.type in('U','G') then 'from windows ' else '' end +
  'with ' +
  case when p.type = 'S' then 'password = ' + master.sys.fn_varbintohexstr(l.password_hash) + ' hashed, ' +
  'sid = ' + master.sys.fn_varbintohexstr(l.sid) + 
  ', check_expiration = ' + case when l.is_expiration_checked > 0 then 'ON, ' else 'OFF, ' end + 
  'check_policy = ' + case when l.is_policy_checked > 0 then 'ON, ' else 'OFF, ' end +
  case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else '' end 
  else '' end +
  'default_database = ' + p.default_database_name +
  case when len(p.default_language_name) > 0 then ', default_language = ' + p.default_language_name else '' end
FROM sys.server_principals p 
LEFT JOIN sys.sql_logins l ON p.principal_id = l.principal_id 
LEFT JOIN sys.credentials c ON  l.credential_id = c.credential_id
WHERE p.type in('S','U','G')
----AND p.name ='reader' ----logins name

---

如果您觉得我的回复帮助你解决了该问题，请帮忙‘标记为答案'以帮助其他社区成员迅速找到有用的答复。
如果没有，请回复并告诉我们当前情况，以便提供进一步的帮助。

Answer 2

已验证，可用，谢谢！