Back up and Restore

[AndyLord]

Can anyone help with this Urgent issue.
I have been given the task of creating a new SQL Server 2000 database, from an existing database server to another server.
I have created a backup, by selecting the database then selecting All Tasks then Backup Database, of the existing database, which worked fine.
I have then restored the backup, using the same as above but using the Restore option, onto another server which seems to work fine. But on closer inspection, via Enterprise Manager, the database users appear not to have been copied across. The tables, Stored Procedures and Database Roles are fine but most of the Database Users are missing. When I try to create a new database user, giving the required name, I get a message that this user already exists but it does not appear.
Is this to be expected, if so how can I obtain all the users from the original database into this new one.

Any help with this issue would be greatfully received as this has become urgent issue to resolve as some users can not use the new database on the new server.

Andrew Lord

 

[JamesLean]

You have what are called "orphaned users" and is a common occurance when you transfer a database as you have described. Basically the users do still exist in the database but the links between them and the logins are broken.

To fix this problem, use sp_change_users_login (look it up in BOL for full details). Basically run a report first to see the orphaned users:

EXEC sp_change_users_login 'report'

and for each one that appears run:

EXEC sp_change_users_login 'update_one', 'user', 'login'

to link them back to the correct login.

--James