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, run either one of the following in the target database:

sp_change_users_login ‘Auto_Fix’, ‘my_user_name’

sp_change_users_login ‘update_one’, ‘calvin’, ‘calvin’, ‘password’
— this command will link the server user to the database level user.

Advertisements
This entry was posted in SQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s