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 (using sp_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_users

ON ALL SERVER

FOR ALTER_DATABASE

AS

execute sp_change_users_login 'update_one', 'easyris_41', 'easyris_41';

Someone an idea how to automate these usermapping after failover on the (new) principal server?

Based on:

http://www.microsoft.com/technet/prodtechnol/sql/2005/mirroringevents.mspx#EDBAC

you can be set up to be notified (or run a job or particular sp) when the server becomes the principal.

Thanks,

mark

|||

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

|||I am just starting to look into mirroring and had run across this same problem. I have not had a chance to look into this but supposedly there is a task in SSIS that allows you to transfer logins from one server to another.
|||

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,

|||

Hi Anders,

Did you ever got this to work? Do you know of any documents that walk you thought on how to do this? I am new with SSIS.

Thanks

Rick

|||

Hi Rick,

Yes, it's been up and running for almost a year now.

Im sorry i dont have a walk through for you but the SSIS bit is pretty straight forward.

These documents should get you started.

Transfer Logins Task

http://msdn2.microsoft.com/en-us/library/ms137870.aspx

Set Task Properties

http://msdn2.microsoft.com/en-us/library/ms139733.aspx

/Anders