none
Create SQL user on Availability group database and permission not found after fail over RRS feed

  • Question

  • Hi we have always on 2016 R2 and two databases on added to always on group. now The application users create instance level and granted on primary replica(db_datareader) , This user has create on secondery  replica as well. When failover to secdery replca the usr get following error message as not accessible.  But db level ( user security I can see the same user already on) but instance level permission lost as image on secondery  node  . But when fail back to primary node  the user can access. Noy sure why is this happen . Cant redo grant  manual permission again and again. any idea. 



    • Edited by ashwan Thursday, August 22, 2019 6:28 AM
    Thursday, August 22, 2019 6:26 AM

Answers

  • Hi I found the issue . The issue is (sid numbers are different on both server ) I created same user on second replica with same sid no retrieve from primary node. Create user and granted perm on second node. Aftr failover fail back , permission remain same . 

    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'

    • Marked as answer by ashwan Thursday, August 22, 2019 12:47 PM
    Thursday, August 22, 2019 12:47 PM