Connect to SQL Express 'User Instances'

Introduction

In the free SQL Server 2005 Express Edition, you get a new connection string parameter 'User instance=true'.

"data source=.\SQLEXPRESS;Integrated Security=SSPI;
   AttachDBFilename=|DataDirectory|people.mdf;User Instance=true"

When this parameter is specified, SQL Express will create another instance of the sqlservr.exe process on the client. This process runs in the security context of the currently logged on user. The connection object passed back to the client is connected to this new user child instance, running as a client process in the user's security context.

More details about User Instances? Read this excellent article on MSDN: Working with User Instances.

SQL Profiler

For monitoring purposes (what's going on under the hood, troubleshooting, and more), the SQL Server Profiler acts as a big time saver. The Profiler is able to read and display all the traffic between the client (e.g., a custom-application, SQL Management Studio etc.) and the SQL Server. Just attach the Profiler to a running SQL Server instance.

But, how do we attach to a 'user instance' where you don't have a well known instance name?

First, query the master database from SQL Express as follows:

Here, you get the named pipes per instance. You get the heart-beat as well: alive or dead (if the lifetime of a User Instance Connection has timed out; default is 60 minutes).

Second, connect with the pipe (only alive instances can be connected):

Third, get the traffic, sniff, profile...

You get the Profiler together with the Workgroup-, Standard- and Enterprise editions.

SQL Server Management Studio / SQL Server Management Express

This solution will work for SQL Server Management Studio as well:

The Management Studio does display all the attached databases (in my case, AspNetDB.MDF and People.MDF). Here is the free version of the SQL Express Studio Management Server.