User Instances in Sql Server Express

This might be a simple solution, but when I was first starting Sql Server development, it was a difficult problem to fix. User instances are new to Sql Server 2005 and the easiest definition is that user instances allow those who are not administrators to attach a Sql Server database to an application. This is necessary if you are attaching a database using Visual Studio rather than the Sql Server Management Studio. It’s only enabled in the express edition, so if you’ve spent money on the full version of Sql Server, don’t worry.

I first noticed this when I tried to attach a new Sql Server database to an app I was working with a while back. Here’s how you fix it:

The easiest way to solve this problem is to se the Sql Server Management Studio.

1. Log in to Management Studio (Regular or the Express version, it doesn’t matter). Connect to the database engine for your local machine. Generally you can use the Windows authentication, although if you are managing the server remote, you’ll have to use the Sql Server Authentication.

2. Click the button to create a new query.

3. In the query box, type the following:

sp_configure¬† ‘user instances enabled’, 1; RECONFIGURE

4. Right mouse click and select the option to run the query.

5. It should come back successful

The only other way to change this is through sp_configure. You can click here for more information.