When upgrading to a new version of SQL Server, moving the databases themselves is pretty straightforward–you can use either backup and restore, or detach and attach. However, you’re likely to end up with orphaned users if you do this. Recreating the users manually is tedious (especially when you consider tracking down and/or changing all the passwords) and may cause SID conflicts. Fortunately, Microsoft provides a helper script in this knowledgebase article:
Some things to be aware of:
- You need to review and edit the output of this script as it will capture all logins, including some you likely don’t want to recreate (built-in accounts, Windows groups, plus “real” accounts that may be dead wood).
- Even though the page specifically says
“The steps in this article do not transfer the default database information for a particular login. This is because the default database may not always exist on server B. To define the default database for a login, use the ALTER LOGIN statement by passing in the login name and the default database as arguments.”
the resulting CREATE LOGIN scripts do include the DEFAULT DATABASE parameter. As they indicate above, this can cause problems if the database isn’t present. I set most of mine back to “master.”
I run this, selectively, before restoring target databases and everything “just works.” Used judiciously, it’s a real time-saver.