SQL2000, restore DB with user security attached 2

[toetag]

Server 1: production SQL 2000
Server 2: testing SQL 2000

fairly regularly, I'm asked to back up the database on Server 1 and restore it unto Server 2. I just right click, all task, backup database on server 1. On server 2, right click, all tasks, restore database using the file from server 1.

However, once this is complete i have to manually recreate all the users (it's testing, so perhaps 30 of my 600 test against this system).

I've tried using the copy objcets to just get the user and security settings. I get all the logins but their rights to the DB are gone. I have to delete the user rights under the DB then add them back.

I understand why logically; different objects, different SIDS (or whatever the term is used for the object references).

Am i missing something? Is there an "easy" way to get everything to restore without me having to spend an extra hour or two fixing user logins?

"The only desert to an Irishman is an empty glass".

 

[ptheriault]

yes you are missing something. After the restore run.

sp_change_users_login 'REPORT'

this will show you all the logins that have out of sync SIDS.

to fix them run

sp_change_users_login 'update_one', 'login', 'login'

This will correct the logins and you won't have to drop them and re-add.


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

Also,
What you should do is recreate your logins on the test server with the same SIDS. Then you won't have to run sp_change_users_login. Here is an FAQ on how to do that.

faq962-6608

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[toetag]

Thank you ptheriault. Your last post was exactly what I needed. I just created the SP and ran the results against my training DB (i cleared all of the manually added logins first). Everything worked like a charm.

Much Thanks ptheriault!

"The only desert to an Irishman is an empty glass".