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?
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.
- Restore Database User to Server Login on SQL Server 2008
- User not functioning after backup and restore of database to another server in SQL Server 2005
- SQL Server 2008: how to add logins to database
- How do I remove Slony from a restored PostgreSQL database?
- moving postgresql database from osx to windows 7
Leave a comment
- Cron expression that runs every 5 minutes from 1:30 am – 6:00 am [duplicate]
- Understanding redundant power supplies
- Is there a way for administrators to disable users from installing Firefox extensions?
- Is there research material on NTP accuracy available?
- How to create a limited “domain admin” that does not have access to domain controllers?