When I restore a db in Sql Server 2008 R2 from data on another server, it makes a mess of the users. I have a Windows User and MsSql Login named Web_SqlA on both machines. Before the Restore, Web_SqlA is properly mapped to the right Windows user in the database. After the Restore, Web_SqlA is still listed as a user for the db, but it's no longer tied to the Windows user, causing Trusted Connections to it to fail.
How can I Restore the db without breaking this user each time?
I see that this:
Sql Server Database Restore
Addresses fixing these orphaned users after the fact; I'm looking to prevent overwriting the users during the Restore in the first place - everything else should be restored, but leave my users be. How can I go about that?
If you use sp_help_revlogin before doing the restore, this will allow the users to match up with the logins. It'll take a little bit of planning...
http://support.microsoft.com/kb/246133