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?

  • Looks like both of those links point to the same question - are they supposed to be different? – dsolimano Jun 30 '12 at 21:01
  • Ah - yes, thanks, clearly pasted one link twice. I'll edit to leave just the one. – Chris Moschini Jul 1 '12 at 18:20

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...


  • Thanks! That article is for 2005; this appears to be updated for 2008: 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 1 '12 at 18:59
  • This modified version of the SP apparently uses a simplified syntax for these Windows users that get corrupted: 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 1 '12 at 19:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.