Sunday, February 19, 2006 6:02 PM
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?
Sunday, February 19, 2006 6:11 PMis the database on your local machine or are you trying to connect through remote machies. Remote connections will not be allowed.
Sunday, February 19, 2006 6:30 PMLocal database that I will eventually deploy with the product for local use only.
Sunday, February 19, 2006 9:02 PMTry 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.
Sunday, February 19, 2006 11:17 PM
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?
Monday, February 20, 2006 2:23 AM
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
Monday, February 20, 2006 2:28 AMLot'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.
Wednesday, February 22, 2006 1:15 PM
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..
Monday, March 13, 2006 11:05 AMHi,
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.
Saturday, May 20, 2006 10:46 PM
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?
Monday, May 22, 2006 4:11 AMThanks kabalweg for the str8 forward solution to this issue. It worked for me and made complete sense as soon as I read it.
Thursday, June 22, 2006 6:32 PMit'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!-)
Friday, March 23, 2007 5:01 PMGod bless you mate, you gave me heaven on earth. wonderful, amazing, magical. It works like .....wawwwwwww
Saturday, March 31, 2007 9:33 AMwoot!!dude..u simply rocks!!
Sunday, April 15, 2007 8:32 PM
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?
PS i have been restarting my sql service through the sql server configuration manager
Monday, April 16, 2007 4:17 PMThis advice was spot on and resolved my issue instantaneously. Thanks, KaBalweg
Wednesday, April 25, 2007 5:42 PM
escribiendo en el query editor:
exec sp_configure 'user instances enabled', 1.
sólo que no sabía cómo "restart"
así que pinché el botón execute
Friday, May 04, 2007 6:45 AM
Thanks dude for ur fanstatic str8 answer.It helped me a lot and solved many problems.
Saturday, June 16, 2007 11:18 AM
I entered the previous code in a new query in the Managment Studio Express and it showed as sucessful. But when I run Visual studio I still cannot not add any sql datasource with out getting the same error.
I tried running the Surface area configuration where it allows me to have Admin privleges as well but it does not save them. I am just trying to learn visual studio and am using this on my local machine. I have looked at threads for hours trying to figure this out but I can't
Saturday, June 16, 2007 9:37 PMtrata de abrir la carpeta con el right button and change security settings
Tuesday, July 10, 2007 4:40 PMI had an issue installing IssueManager and searched all over the web, finanlly found something that worked!
Wednesday, August 29, 2007 7:57 AMIs there any way that i can execute this command to a machine that has no sql management studio express installed in it?
perhaps putting it into a exe file written in vb.net?
i've got a vista user (who doesnt have sql management studio express installed) that somehow caught with this problem.
Saturday, December 08, 2007 6:59 PMthanks kabalweg!
Tuesday, February 12, 2008 11:12 PMGreat answer! Thanks... back on may way...
Saturday, March 08, 2008 1:18 AMwhen you log on to SQL Server Management Studio Express check to see which profile you are loggin in under. I had the same issue and realized that I needed to log in to the profile that looked like profile/SQLEXPRESS and change that one.
See if that worked... good luck!
Check out my sites or contact me at em with more questions, Bellingham Promotion and Web DevelopmentJL
Wednesday, May 07, 2008 4:36 PM
Solved my problem as well. Thanks
Sunday, May 25, 2008 3:57 PMI had the same problem and this solution also worked for me.
Thursday, June 12, 2008 12:17 PM
Follow the steps exactly. There's no way it won't work
Sunday, July 13, 2008 3:41 AM
Thanks for posting such a nice solution. It resolved my problem instantly.
May God Bless you,
Wednesday, July 30, 2008 12:13 PMFYI Your solution was right-on. I have both SQL Server 2005 Professional and Express version and was starting my first Express project.
John Wm Beckner
Sunday, August 17, 2008 2:31 PM
KaBalweg: thanks, thanks, thanks and 1.000 thanks.
That was wonderful!!! you save me a lot of time!!!
Wednesday, August 20, 2008 7:00 AM
I see this post has already resolved the issue at hand. In my case, the problem is that every time the computer is restarted the setting is reset back to zero. I created a BAT file, which I'd like to share, to resolve this quickly until a new way to deal with this arises.
These are its contents:
@ECHO ::: Enabling User Instances
sqlcmd -S .\SQLExpress -Q "sp_configure 'user instances enabled' , 1;"
@ECHO ::: RECONFIGURING
sqlcmd -S .\SQLExpress -Q "reconfigure;"
@ECHO ::: COMPLETED
Create a BAT file and _edit it_, copy paste the 'code' above in it and run it whenever you have an issue.
sqlcmd :: sql command prompt
-S :: server setting
.\SQLExpress :: the sqlexpress server
-Q :: execute literal command and exit
"sp_configure 'user instances enabled' , 1;" :: the command to enable user instances
"reconfigure;" :: reconfigure for new settings to become active
@ECHO :: some feedback won't do any harm
PAUSE>NUL :: PAUSE while passing (>) feedback to NUL output
Tuesday, October 28, 2008 4:17 AM
This changed the connection only to show an error that failure in starting the process for the user instance. The connection will be closed.
Wednesday, November 26, 2008 4:17 PMYeap, that worked...thanks!
Friday, December 12, 2008 1:59 AM
if you have sql server2008 developer edition :
1)create "aspnetdb" database with "aspnet_regsql" in vs command prompt.
2)find location of " aspnetdb.mdf" and " aspnetdb_log.ldf " files with right click on database name in mss management studio.
3)stop sqlserver service.
4)copy two files .mdf and .ldf in App_Data folder of your site.
5)start sqlserver service.
6)attach aspnetdb database from files to sqlserver.
6)chang in machine.config file instance to false:instance=false.
7)press asp.net configuration in your project.
8)define your users and roles.
make sure to create ur website project in "C:\Documents and Settings\username\My Documents\Visual Studio 2008"
roozbeh noroozy GIS Developer in IRAN -Tehran: firstname.lastname@example.org
with thanks from:
Monday, December 15, 2008 3:01 PM
It worked like a charm. Thank you for your help.
Friday, April 09, 2010 6:15 PMThanks kabalweg your suggestion worked. I was missing Reconfigure. The moment I used that things worked.
The faith in your methods are tested the most when the horizon before you is the darkest
Monday, July 19, 2010 10:17 AM
i was having the same problem.
now it is solved as your suggestion.
Tuesday, October 19, 2010 2:58 AM
I've tried the above and it says i have a successful query however the message states:
Configuration option 'user instances enabled' changed from 1 ro 1. Run the RECONFIGURE statement to install.
What's that?? I thought typing "RECONFIGURE at the end was what i was suppose to do?
Also, i also read in some similar forums that said i should delete C:\Documents and Settings\user name\local settings\Applocation Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS.
When I try to find that file, I make it up to the Microsoft folder and then I don't have the rest of those files in my system?
I've tried everything all of these forums recomment and i still don't seem to get anywhere. I still get the following message in VB2005Express:
Generating user instances in SQL Server is disabled. Use sp_comfigure 'user instances enabled' to generate user instances.
And then here i am again trying to figure out how to enable them. I tried doing it throgh command prompt and it gave me the same message as Mgmt. Studio did.
Sunday, November 07, 2010 7:27 PMThanks, it worked for me. Damn this Troelsen with not working examples.
Thursday, December 08, 2011 5:17 PMThanks for the help!