May 30, 2012
tom
No Comments

Moving database to new server

Question

I’m restoring a backup of a production database to a new server. The new server also hosts the application which makes use of the database. As such I’d like to use the same user/pass combination to connect to the database. The user is also included in the database backup so I thought that should work. However the user doesn’t exist as a (database) user on the new server. So I created a new server user and tried to map this credential to the newly restored database. However this fails because the user already exists in the database.

Is it possible (and if so please tell me how) to move/copy a user from a database (server independent) to the list of (database) server logins?

Asked by user10893

Answer

In SQL-Server 2008 the database users for a restored database and server logins may be out of sync because the internals IDs do not match. To fix this run:

EXEC sp_change_users_login @Action=”Update_One”, @UserNamePattern=’databaseusername’, @LoginName = ‘logonname’;

against your restored database and for each user/login.

In SQL-Server 2012 there is a new option to use database users independent of server logins.

Answered by Peter Hahndorf

Check more discussion of this question.

Related posts:

  1. Restore Database User to Server Login on SQL Server 2008
  2. User not functioning after backup and restore of database to another server in SQL Server 2005
  3. SQL Server 2008: how to add logins to database
  4. How do I remove Slony from a restored PostgreSQL database?
  5. moving postgresql database from osx to windows 7

Leave a comment