none
always on发生故障转移后,dbdatareader固定数据库角色丢失? RRS feed

  • 问题

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

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

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

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

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

    2020年9月18日 1:07

答案

  • 你好,

    发生此问题的原因可能是每个服务器上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


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


    2020年9月22日 7:46

全部回复

  • 你好,

    发生此问题的原因可能是每个服务器上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


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


    2020年9月22日 7:46
  • 已验证,可用,谢谢!
    2020年9月28日 6:29