SQL Server 2005: Restore database with different users/logins
Posted on 2009-05-26
In brief, I have a scenario where I want to restore a database backup as a different database, plus also change the owner of the objects and the logins that are associated with the new database.
In more detail:
I have a database called cyber. I have two logins: cyber and cyber_report. In addition to the normal users (eg: dbo, guest, etc) the cyber database has two users: cyber and cyber_report. These two users are associated with the respective logins of the same name. Also, there are two schemas: cyber and cyber_report. The main difference between the users cyber and cyber_report is that cyber user owns all the tables, stored procs, etc. The cyber_report owns a bunch of views which are used by the reporting engine (Crystal Reports). The application for which this database is the back end connects using the cyber user.
I want to backup the cyber database, and restore it as cyber_dev. Just like above, there will be two logins: cyber_dev and cyber_dev_report. The cyber_dev database needs to have two users and two schemas: cyber_dev and cyber_dev_report.
The straight restore part I can do without too much drama. But when I do, all the tables etc are still owned by cyber (not cyber_dev). I want to be able to either some how "map" the old logins/users/schemas to the new ones; or alternatively run a script after the restore which "fixes" the new database.
This is somewhat urgent - please help!