Login in server suddenly lost mapping to their Users in databases 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.
Since an Availability Group is just a container of databases, you should treat users and logins the same way you would for a mirrored database, log shipping, etc. And that is that the logins on a secondary server must be created with the same explicit SID as the logins on the primary server. Otherwise the link between the server-level login and the database-level user will be broken upon failover - which I suspect is what happened here (I'm not going to try to retrace all of your steps and figure out how many times you failed over and back - let's focus on preventing this from happening again).

You say that you re-synced with the SIDs, but I don't know what that generic phrase means. You should include the code that demonstrates what you did, because if it isn't doing what you think it's doing, we may be able to tell you why, but we can't do that with just hand-waving.

The script I think you should be using is the one from Robert Davis, which Mike Walsh describes here (I'm not going to copy the code here):


Transferring Logins to a Database Mirror


There is also an SSMS add-in written by Jonathan Kehayias that helps automate synchronization of things like logins and jobs across all the nodes involved with an availability group:


Synchronize Availability Group Logins and Jobs
SQL Server Management Studio Availability Group Add-in


However, this add-in only works in SSMS 2012/2014.

Length of time is irrelevant here. You had a year of uptime. Yay! You could failover tomorrow and be right back in the same boat.