Enable User Instances in SQL Server

I'm just starting out and trying to connect to my first database using the following string:

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\RFPdb.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True

I get the following error during debug:

"Generating user instances in SQL Server is disabled. Use sp_configure 'user instances enabled' to generate user instances."

I've done this on every database - Master, Model, Tempdb, etc, and my database.

Still get the error. I may not be enabling it correctly. Any help would be appreciated.

The documentation suggests that I use user instances so that users without Admin rights can work with the database. Add, delete, edit data. Is this true?

is the database on your local machine or are you trying to connect through remote machies. Remote connections will not be allowed.|||Local database that I will eventually deploy with the product for local use only.|||Try using User Instance=False. User Instance is a special feature in express edition that allows normal users to have owner privilges and I guess this is not what is require here.|||

When I turn User Instance Off I get the error:

"A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."

I tried downloading SSEUtil to set enable user instances but the .exe doesn't work. It flashes a command window and shuts down. Any ideas on this?

|||

is it necessary to use to attachdb. I mean any specific reason. If not try connecting using a connection string like :

connectionString="Data Source=LPXP561;Initial Catalog=AdventureWorks;Integrated Security=True

|||Lot's of other options will work. I'm trying to use the new User Instance feature of SQL Sever Express. It requires the "attachbd" usage and User Instance = True. I can't get it to work as decribed in the documentation.|||

Have got the same problem: cant connect to user instance, since it is not enabled in express version by default..

can anybody suggest how to "Use sp_configure 'user instances enabled' ", where to type it.. or whatever..

|||Hi,

Open the SQL Server Management Studio Express. This is the downloadable program in the same site where you downloaded the SQL Server 2005 express used to manage SQL Server 2005 Express.
In the query editor type this text: exec sp_configure 'user instances enabled', 1.
Then type: Reconfigure.
Then restart the SQL Server database.

Good luck.

KaBalweg?|||

Hi DDH,

I know this is now several weeks after your posted problem, but I have exactly the same issue. Did the suggested solution resolve the problem?

Mike

|||Thanks kabalweg for the str8 forward solution to this issue. It worked for me and made complete sense as soon as I read it.|||it's really nice when someone gives you a straight answer!-)
this one got me going right away!
i was trying to connect to the DBs I'm building in SQL 2005.
"exec sp_configure 'user instances enabled', 1" got me started quickly.
heck! ...i might re-visit this forum!-)
|||God bless you mate, you gave me heaven on earth. wonderful, amazing, magical. It works like .....wawwwwwww|||woot!!dude..u simply rocks!!|||

Hi, I have been doing this, and in the query editor, it says it has been changed, and then restarted the server, but it neva worked.

So i tried it again (and the query results said changed from 1 to 1), but it still hasnt worked.

Have i configured the sql server incorrectly?

Please help

PS i have been restarting my sql service through the sql server configuration manager