We are running a two node clustered database with HA enabled.
I'm wondering if someone has figured out a way to preserve the users when a HA failover occurs. Right now, the process is to run the secure check job and drop and re-create the users in security. This will allow the secondary to become the primary and resume Tessitura operations.
I think the UID assigned to the users changes when dropping and re-creating. Thus, even though user names and credentials are duplicates between the two nodes, the UID's are different. Has anyone overcome this with a job copy or something similar?
We do this by having 2 scheduled jobs.
The first job uses an amended version of the stored procedure sp_help_revlogin (https://support.microsoft.com/en-us/kb/246133) to generate a create script for each of the logins. These create scripts are stored in a local table in the master database on each instance.
The second job then compares the values in the local table with the one on the primary replica and drops and recreates any logins where they don’t match.
By using the create script generated by sp_help_revlogin on the primary, it updates any changes in passwords and keeps the SID of the logins consistent across all instances.
We run both of these jobs once a day in the evening, but I guess there’s no reason why they couldn’t be run more frequently.
Hope this helps