Monthly Archives: August 2011

SQL Server Upgrade – Transfering User Names and Passwords

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:

How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008

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.

How to Show Removed Devices in Device Manager

Admit it, sometimes you uninstall hardware without removing the driver first. Sometimes the hardware just dies and Windows makes it “go away.” The problem is, the driver and configuration are still there, and if you were using the default Microsoft drivers, there’s no visible way to uninstall those.

Here’s a workaround:

  1. Obtain an administrative command prompt (Start|All Programs|Accessories, right click on Command Prompt and click Run as Administrator)
  2. At the command prompt, type or paste:
    set devmgr_show_nonpresent_devices=1
  3. Then type:
    devmgmt.msc
  4. This will pop up a normal-looking Device Manager, in the menu of which click View|Show Hidden Devices
  5. Expand the relevant part of the tree and (with care) uninstall away!