Wednesday, October 29, 2008

Fix user mapping after restoring database to another server instance

Often when you restore a backup from SQL server you run into funky
problems with users. Suppose you have login calvin and you restore a
database from another server that already has user called calvin. When
you try to map the server calvin to the database calvin, you might
get the error:

Error 15023: User or role 'calvin' already exists in the current database.

To fix this:

sp_change_users_login 'update_one', 'calvin', 'calvin', 'password'
-- this command will link the server user to the database level user.

No comments: