Click here to Skip to main content
Click here to Skip to main content
Go to top

Connect to SQL Express 'User Instances'

, 27 Aug 2006
Rate this:
Please Sign up or sign in to vote.
Manage and profile 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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Patrick Spieler
Web Developer
Switzerland Switzerland
Consultant for Trivadis AG in Switzerland.
Interested in .NET, Architectures, Patterns and Web 2.0.
Blog and articles at http://sharpcuts.blogspot.com

Comments and Discussions

 
QuestionNo tracing details Pinmemberishimakmola27-Jul-14 10:48 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 27 Aug 2006
Article Copyright 2006 by Patrick Spieler
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid