Refreshing 200+ Passwords 1

[fuzzyocelot]

Hello all!

We have an automated process that copies SQL backup files from our production SQL Server 2000 server (sp3a) to a disaster recovery server (also SQL Server 2000 sp3a) nightly. There is a job that restores all 100+ databases on the disaster recovery server and fixes the orphaned users.

When the password for a SQL login changes in production, we manually change it on the DR server as well. However, we suspect some passwords are out of sync. Meaning, they were not changed on the DR server.

I'm afraid I may already know the answer to my question, but felt I should take a chance and ask anyway. Since we have over 200 SQL logins on the production server alone, is there a way to refresh the passwords on the DR server using the passwords from the production server without doing it manually?

My current thought is that I'm going to have to drop the databases on the DR server, drop the logins, recreate the logins using sp_help_revlogin on the prod server, restore the databases, and fix the orphan users. Is there a better way or is this pretty much my only option?

Any help is greatly appreciated!

Thanks!

 

[ptheriault]

Using sp_help_rev_logins would have been my suggestion. I don't think you have any other option.

- Paul
- Experience is the name every one gives to their mistakes.

 

[fuzzyocelot]

I was afraid of that. Thanks, though!