2

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?

Community
  • 1
Chris Moschini
  • 469
  • 1
  • 8
  • 16

1 Answers1

2

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

Peter Schofield
  • 1,639
  • 9
  • 11
  • Thanks! That article is for 2005; this appears to be updated for 2008: http://support.microsoft.com/kb/918992 After copy/pasting that updated sp_help_revlogin code in to add it to master's SP's, I was able to generate the command(s) for recreating users after the fact. Since the Restore creates broken versions of these same users, I needed to prepend the generated script with: drop login [Web_SqlA]; etc, to clean out the broken logins the Restore creates before replacing them with fixed users. – Chris Moschini Jul 01 '12 at 18:59
  • This modified version of the SP apparently uses a simplified syntax for these Windows users that get corrupted: http://blogs.msdn.com/b/lcris/archive/2006/04/03/567680.aspx For example it changes the login creation to simply CREATE LOGIN (user) FROM WINDOWS – Chris Moschini Jul 01 '12 at 19:05