Mine is Always ON availability environment. I have 3 SQL 2014 servers(Primary and 2 replicas). Since one year all the logins working FINE in the servers. Some how suddenly, I am getting errors from the Application server stating that the PIDS were not able to access the Database. The PIDS are successfully logging in the server but not able to access database. I checked the mappings of the login in the server. I seen no database was checked. I dont know how it lost its mapping to user in the database.
When I checked for the user in the database, I can see that user exist in the database and has all the required privileges assigned to that user too..
NOte: I tried to restart the server that which fix the problem for 2 hours and again we ran back to the same issue. I droped all those users(who lost mapping from Login) and recreated them with all the SIDs synced in all three servers. This fixed my problem for now. but I want to know the root cause of the issue.
Question 1: How my user lost their mappings with login suddenly that which are working absolutely fine since 1 YEAR.
Question 2: How a simple server restart fix the issue for a couple of hours and again why the problem came back.