MS SQL 2005 - Mirroring (password sync)???

[ecka00]

We just started running with a mirror and the first time we moved the mirror we used the login create script which grabs all of the logins and passwords off of the active mirror (primary) so that when we moved the mirror all the accounts would work--now when we go to move back to the original node the password changes will not be current. Anyone have a script to update passwords?

[rmiao]

Where did you make password change? If you have script to grab logins and passwords on primary server, why don't use same script to get those info from mirrored server?

[skhanal]

You have to write a script to regularly generate scripts using sp_help_revlogin in primary site and copy and save it in mirror site. This script needs to be run after database switch over.

[ecka00]

k, maybe if i give an example using server names: we started out on SRV1 as the primary and SRV2 as the mirror. so when we switched to SRV2 as the primary and SRV1 as the mirror we ran the SP_HELP_REVLOGIN to generate all the logins on SRV2. Now that we want to switch back to SRV1 as the primary all of the password changes that took place while SRV2 was the primary will be lost because the script only creates logins--it does not drop logins and recreate them. I realize I could probably resolve this by modifying the script\SP_HELP_REVLOGIN however, someone, if not microsoft, must have addressed this already. I mean what good is a mirror if you can't keep logins and passwords in sync?

[rmiao]

DB mirroring can't mirrow system dbs.

[skhanal]

You can generate drop login statements by querying syslogins view.

[MAK]

Run a nightly job in server1 that generates all the login script including encrypted password and save it on to a file in server 2.

When the system fails over you have to run the previous night script on the secondary server.

[MAK]

Remember. this is database mirroring and not server mirroring.
Also remember the database on the secondary server is in restore mode meaning you can add users to it similar to log shipping.