Problem with 'orphaned' users in mirrored setup

Question

Problem with 'orphaned' users in mirrored setup

Alexander-1013105

SSC Veteran

Points: 247
More actions
February 10, 2009 at 11:40 am

#383908

This is a tricky one, so I hope people can help me with this.
First a disclaimer: I'm not a full-time DBA, and the database has been set-up by a 3rd party SQL server specialist company. I'm asking them this as well, but just in case they're reluctant to give after-sales support, I'm looking elsewhere as well.
Here goes: We've got a fairly simple 3-server setup with (I think) log-shipping and full fail-over. (2 databases mirrored and 1 witness). During testing of the failover, we ran in to a problem that our server application couldn't connect to the 2nd database when we did a manual failover. After lots of testing, we discovered that there was a problem with the user/login credentials. It seems that (somehow) the 2nd database doesn't have the required link between the 'login' objects (which I think of as server-specific) and the 'user' objects (which are database-specific).
After a lot of digging, I ran across an article that mentioned the 'sp_change_users_login' stored procedure. This confirmed (through the 'Report' parameter) that all our users were 'orphaned' (quick aside: we've got 11 users and logins, one for each accessing application). I then tried to re-connect one of the users to its login by using that stored procedure and it seemed to work! Victory Dance Time.
Alas, it was too soon: that same stored procedure turned out to actually sever the connection between the same user and login on the 1st server (which was now mirroring changes from the 2nd server). This baffles me, but I assume it's something to do with the fact that the mirror server tries to replicate the stored procedure but can't find the database somehow... I don't know.
So, my question is: is there a way to restore these severed links (or 'orphans') on the 2nd server without killing the same on the 1st server? Is there a way for example to pause the mirroring and issue those commands without them being flushed when the mirroring is unpaused? I'm loath to actually stop the mirroring altogether because I wasn't the one who set it up in the first place, and I also don't want to delete and re-create the logins and users, as I'm not 100% sure that will work.
Any help or nudges in the right direction would be greatly appreciated.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

The easiest way out of this problem is to script the logins from the principle with the SID included and then recreate the logins on the Mirror with the same SID as the principle. Microsoft has provided a number of scripts dependent on the versions of SQL that do this. A full list of them can be found on the following URL:

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=TransferLogins&referringTitle=Home

Basically, you will need to drop the logins on the mirror and then recreate them with the scripted logins from the principle. This makes failover very simple.

Alexander-1013105 SSC Veteran Points: 247 More actions · Answer 2

Ooh, that looks like a good approach. I'll have a look at it first thing tomorrow and let you know how it goes. Thanks!

Satheesh E.P. SSCrazy Points: 2509 More actions · Answer 3

Even I am facing this issue. We were in the testing phase to implement Database Mirroring. I had built mirroring with a witness to make the fail over automatically. The applications are also designed in a such a way to connect with the mirror db if principal is not available. But due to this login problem, manual intervention will be required. Is there any way to run this script automatically on fail over?

---------------------------------------------------
Thanks,
Satheesh.

Alexander-1013105 SSC Veteran Points: 247 More actions · Answer 4

I'm not 100% sure this is going to solve my issue. I've still got the problem of having the mirroring potentially messing things up on the non-principal server. I'm looking in to it now (just came in to work), so I'll post my findings here.

On another note: I'm just wondering about this detail: in the current setup, the logins have different names to the users. So the login (for example) is called server_XXX_user and the login is called server_XXX_dbuser (a bit confusing, I know). Looking at the 'sp_change_users_login' SP, it's able to automatically re-connect users and logins with the same name. Might the fact that these names are not the same exacerbate the problem?

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 5

Alexander,

if you transfer your logins including the SID, you won't need to use 'sp_change_users_login'. This procedure is usefull if you create a login with the same name like on the primary server, but with a different SID. You can either specify the SID when creating the login throu a script or what I usually do on a mirrored server is to create a TRANSFER Login task in SSIS and schedule the package to run once a day.

This way you can also keep passwords in sync whenever a user changes it.

[font="Verdana"]Markus Bohse[/font]

Alexander-1013105 SSC Veteran Points: 247 More actions · Answer 6

Our problem has been fixed! Thanks for your help Jonathan and Markus. It took me some time to figure out what the hell was happening, but the method you suggested (creating the login using the SID) saved the day.

In short: I created the login and user on the Principal database and then used the stored procedure from the knowledge base article to copy the login to the mirror server. (The user was already on the mirror by virtue of the replication).

Cheers guys, you made my day!

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 7

This is a very common problem in mirrored environments. Keep in mind, that you have to do this for any new logins that you add to the principle in the future as well. They are not mirrored to the mirror automatically, so you need to be dilegent in making sure that you script any new logins and add them via script to the mirror.