CODEWITHSUNDEEP
databasesql-server-2005database-restore
Gautam
Gautam

Reputation: 1740

Restore SQL Server 2005 database without overwriting DB users

I have a database that I constantly need to restore.

Every time I do so, all the users that have access to DB are deleted and I have to manually add them again.

Is there any way that I can restore the DB but the users remain intact or any script that I run before/after the restore so that I don't have to manually add all the users again?

Upvotes: 4

Views: 5166

Answers (2)

Carol Baker West
Carol Baker West

Reputation: 259

Before you restore the database, use this script to script the logins and passwords: How to transfer logins and passwords between instances of SQL Server Then, after you restore the database, execute the created script to re-create the logins

Upvotes: 2

Aaron Bertrand
Aaron Bertrand

Reputation: 280351

Before the restore, you can script the users as they currently exist. In object explorer, expand the database, expand security, highlight the users node and click f8 (object explorer details). In object explorer details, select the users you want to keep, right click and script as create to new window, clipboard etc.

Now run the restore, and when the restore is complete, run the script from above. If this is all happening on the same server, should work just fine as I described. If these are different servers, you may want to look into synchronizing server-level logins first.

Upvotes: 3

Related Questions