March 24, 2022 at 5:46 am
Dear Experts
I have 2 SQL servers set up one as primary avaialblity group and another as secondary. I didnt follow the guidelines where when you create users you create with same SID on the other server. Now everytime the server switches from primary to secondary, the users are not mapped automatically to the correct databases and so the appplication throws error.
From Microsoft website i found this stored procedure EXEC sp_help_revlogin where you can run this and create a script which would create SQL Script for creating users with SID information.
My question is, instead of dropping all users from the secondary database is it possile to just only update the SID alone?
March 24, 2022 at 9:17 am
Hi,
I think, this won't work. Just drop the wrong user, and create the new user with the script and the right SID.
Kind regards,
Andreas
March 24, 2022 at 9:33 am
Thanks Andreas for the reply.
Do I need to update the users in the primary database server, copying it from secondary? As Secondary database server databases are not accessible.
March 24, 2022 at 10:31 am
Hi,
no, do not change the user on the primary.
If this would be my own sql server, I would walk trought this steps.
2. create the t-sql script with the "sp_help_revlogin" on the primary
3. create the user on the secondary with the script generated above
Now, the user from the primary and the secondary got the same SID. After a failover, the application should be able to connect to the database. Is is necessary, because you got the user in the database on the secondary with the wrong SID.
Please test this solution on your test environment first. Every sql server is different.
Good luck,
Andreas
March 25, 2022 at 1:46 pm
my question was same thanks for answer
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply