Create user to access Alwayson readonly databaes

Question

Hi Our env sql server version is 2016 SP2 EE edition always on enabled. So when we create database user for access the report though link server(expected to connect readonly replica databases ) from other remote node then I I noted permission(DB user) has blown away each time when do AG fail over/failover back process. how to maintain permission on READ ONLY site consistently on the DB on the user . At thi stage I cant access ready only site as permission has blow away .( permission granted db_owner)

note: AG already set it as "read intent only"

thing I have tested:
create DB user user with same "SID" on both instance
create DB user user with different "SID" on both instance

no luck any on the way.

any one can help would be great

Answer 1

Hi,

Same logins and map to the same database user on both instance ?

Sync SQL Logins and Jobs

Answer 2

Hi Cris ,same login is there issue is readonly site database unable to access as permission has wiped off. after fail over / fail back both way( AG ) thne peromission will wipe off from readonly site
thank you

Answer 3

Hi I used to login using listener. But I can see when fail over and fail back the AG then read only site will removing the permission from DB users. not sure why is that . I created user with same as problem description. thank you

Answer 4

The proper way to create logins across replicas in a SQL Server Availability Group is by using the article that Criszhan-msft mentioned. However, since you've already created the SQL Server logins on the secondary replicas, you can use the steps outlined in the section Resolve an Orphaned User in this documentation.

Troubleshoot orphaned users (SQL Server)