随笔- 729 文章- 0 评论- 51 阅读- 123万

mssql 2019 Always on后，故障转移后孤立用户问题解决

问题：

1	`数据库配置了Always ON高可用性组。还有的可以访问到主体数据库登录一次，但转移后，就无法登录到备用服务器上的新主体数据库。`

原因：

1 2	`该问题是因为每台服务器上的SQL Server登录的SID（安全标识符）不匹配。尽管对于登录的名字是相同的，但登录经由该login的SID解决。这不是Windows /域用户/组登录的问题，` `因为这些登录的SID是基于域SID为用户/组创建，因此将成为给定同一用户/组相同的，无论添加到什么样的SQL Server用户/组。`

解决方案：

1	`我们需要在secondary replica上面创建SQL Server login，不仅仅具有相同的名称，而且还具有相同SID在主服务器上创建SQL Server登录。下面相关的语句：`

在主库上执行的语句

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<> 'sa'