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?

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

1 Answer 1

Reset to default
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

Improve this answer
2
  • 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, 2012 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, 2012 at 19:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .