Mirroring and orphaned users
Hi there!
There is still a problem with mirrored (mapped) SQL Users. If you mirror a database where an application connects with an sql user, the mapping (login / user) will be lost on the mirror server. After a failover occurs, it it not possible to log onto the new principal database because the database use will be an orphaned user and has to be remapped to the login (usingsp_change_users_login'update_one', 'user', 'user').
Is there any chance to do it in a system trigger? What is the firing event after the failover occurs? I've tried something like following, but it doesn't fire.
alter
TRIGGER map_orphaned_usersONALLSERVER
FOR
ALTER_DATABASEAS
executesp_change_users_login'update_one','easyris_41','easyris_41';
Someone an idea how to automate these usermapping after failover on the (new) principal server?
Hmm, maybe thats a workaround.
But in my mind it doesn't confirm with the requirement of high availability with automatic failover. Furthermore, clients application could run into another exception (Login fails or something like that), what has to be adapted in applications what are designed for high avilibility.
Otherway all the stuff works with domain users. So it seems like a real bug. I can't see any conceptional reason for this behavior. Hope that will be fixed in SP2...
Torsten
OK, I am going to try to write a bit.
1. I completely agree that from a customer's point of view, if when using sql logins, if the database fails over and the clients cannot get to the database (for whatever reason. Here it is a mixed mapping from login on a different server to a user in a "new" database ), then the system is not "highly available." So, we haven't made it easy to use mirroring in certain scenarios. I may try to create a script that solves this problem this weekend.
2. On the other hand, this problem has been here at least since 7.0. In log shipping, backup/restore, and attach/detach this problem of moving the database to a different server where the meta data in master and msdb are different on different servers. The problem is that we have made moving a database to a different server so much easier that the problems that were small earlier are becoming bigger.
So, this really isn't a bug IMHO, but it is unexpected behavior (which is still bad from a customer's point of view).
Thanks,
Mark
Hi guys,
I've asked MS about using sqlusers on a mirrored database and got this reply from Hugo Nunes, support Engineer at Microsoft.
In order to avoid orphaned users on the mirror database you’ll have to copy the logins corresponding to the database users, to the standby server. The copy must be made before restoring the database on the standby server. You’ll also need to copy the user with his SID. The Transfer Logins Task from SSIS performs that task for you as long you set the CopySids property to true.
It works.
yes. but it doesn't solve the issue of default database for a login,
which leads to the same issue - login does not work.
Hi to All,
We are currently having issues like that before, the fix that we do is adding a short dynamic script that will remove orphaned user by executing sp_change_users_login 'update_one', '<user1>','<user1>' on the mirrored database everytime a switch occur.
hope this helps!
regards,
