Just drop all users after restore (see the commented part of my last reply here for a script to do this). They have been scripted out anyway. When you recreate the users will remap to the existing server logins on the new server.
Hey all. There have been quite a few comments recently, and some edits provided by Andrew G last year (I'm a bit behind, eh?). I am reviewing these and will try to incorporate them into the script, and will attempt to the update published on the existing link. Appreciate that everyone is getting good use out of the script.
Can you please suggest in running the same script on all databases in one go.
Unfortunately, this isn't designed to run that way since it is generating print statements. It would take a bit of work to convert over to SELECT statements, or to wrap it up into a procedure and test (there are A LOT of single quotes in this script to escape). I'll see if I can give it a shot in the future, but the use case hasn't been prevalent enough for my day-to-day use to warrant updating it. Happy to take suggestive edits if you're willing to give it a shot.
Thanks Steve! Yup, those are the 4 sections where I added types 'E' and 'X' and it did capture the SQL MI users too. On a side note, I have to run the script again after the database restore just to get the orphaned users. Your script was really great! Thank you for sharing it to everyone.
Love the script, very helpful for restores, but I am trying to edit it to work with just one account that I need to replace throughout many servers. I wonder if this was set up before as I saw "@olduser" listed in a where statement.