none
Enable User Instances in SQL Server

    Question

  • 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:02 PM

Answers

  • 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™
    Monday, March 13, 2006 11:05 AM

All replies

  • is 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:11 PM
  • Local database that I will eventually deploy with the product for local use only.
    Sunday, February 19, 2006 6:30 PM
  • 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.
    Sunday, February 19, 2006 9:02 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?

     

    Sunday, February 19, 2006 11:17 PM
  • 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:23 AM
  • 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.
    Monday, February 20, 2006 2:28 AM
  • 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..

    Wednesday, February 22, 2006 1:15 PM
  • 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™
    Monday, March 13, 2006 11:05 AM
  • 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

    Saturday, May 20, 2006 10:46 PM
  • Thanks kabalweg for the str8 forward solution to this issue. It worked for me and made complete sense as soon as I read it.
    Monday, May 22, 2006 4:11 AM
  • 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!-)
    Thursday, June 22, 2006 6:32 PM
  • God bless you mate, you gave me heaven on earth. wonderful, amazing, magical. It works like .....wawwwwwww
    Friday, March 23, 2007 5:01 PM
  • woot!!dude..u simply rocks!!
    Saturday, March 31, 2007 9:33 AM
  • 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

    Sunday, April 15, 2007 8:32 PM
  • This advice was spot on and resolved my issue instantaneously.  Thanks, KaBalweg
    Monday, April 16, 2007 4:17 PM
  • Problema solucionado,

    escribiendo en el query editor:

     

    exec sp_configure 'user instances enabled', 1. 
     Reconfigure

     

    sólo que no sabía cómo "restart"

    así que pinché el botón execute

    THANKS

     

    Wednesday, April 25, 2007 5:42 PM
  • Hi,

         Thanks dude for ur fanstatic str8 answer.It helped me a lot and solved many problems.

        

         Cheers,

         Avinash

     

    Friday, May 04, 2007 6:45 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 11:18 AM
  • trata de abrir la carpeta con el right button and change security settings
    Saturday, June 16, 2007 9:37 PM
  • I had an issue installing IssueManager and searched all over the web, finanlly found something that worked!
    Tuesday, July 10, 2007 4:40 PM
  • Is 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.

    thnx.
    Wednesday, August 29, 2007 7:57 AM
  • thanks kabalweg!
    Saturday, December 08, 2007 6:59 PM
  • Great answer!  Thanks... back on may way...

     

    Tuesday, February 12, 2008 11:12 PM
  • when 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!

    Jason


    Check out my sites or contact me at em with more questions, Bellingham Promotion and Web DevelopmentJL
    Saturday, March 08, 2008 1:18 AM
  • Solved my problem as well.  Thanks

    Wednesday, May 07, 2008 4:36 PM
  • I had the same problem and this solution also worked for me.
    Thanks!!
    Sunday, May 25, 2008 3:57 PM
  • Follow the steps exactly. There's no way it won't work

    Thursday, June 12, 2008 12:17 PM
  • Hi Kabalweg,

    Thanks for posting such a nice solution. It resolved my problem instantly.

     

    May God Bless you,

     

    Thanks,

     

    Niloy

     

    Sunday, July 13, 2008 3:41 AM
  • FYI 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

     

    Wednesday, July 30, 2008 12:13 PM
  • KaBalweg: thanks, thanks, thanks and 1.000 thanks.

    That was wonderful!!! you save me a lot of time!!!

    Sunday, August 17, 2008 2:31 PM
  •  

    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 OFF
    @ECHO =================================
    @ECHO ::: Enabling User Instances
    @ECHO =================================
    sqlcmd -S .\SQLExpress -Q "sp_configure 'user instances enabled' , 1;"
    @ECHO.
    @ECHO =================================
    @ECHO ::: RECONFIGURING
    @ECHO =================================
    sqlcmd -S .\SQLExpress -Q "reconfigure;"
    @ECHO DONE
    @ECHO.
    @ECHO =================================
    @ECHO ::: COMPLETED
    @ECHO =================================
    PAUSE>NUL

     

    Create a BAT file and _edit it_, copy paste the 'code' above in it and run it whenever you have an issue.

     

    Explanation:

     

    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

     

    Thank you

    Wednesday, August 20, 2008 7:00 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.

     

    any suggestions?

     

    ty

     

    eyveneen

     

    Tuesday, October 28, 2008 4:17 AM
  • Yeap, that worked...thanks!

     

    Wednesday, November 26, 2008 4:17 PM
  •  

    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: roozbeh.n@live.com

     

    with thanks from:

    sanjitpatelhttp://forums.asp.net/p/1090491/2802641.aspx#2802641

    and

    thomassamsonhttp://forums.asp.net/t/913172.aspx?PageIndex=2

    Friday, December 12, 2008 1:59 AM
  •  

    Hi Kabalweg,

    It worked like a charm.  Thank you for your help.

    Jeff

    Monday, December 15, 2008 3:01 PM
  • Thanks 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
    Friday, April 09, 2010 6:15 PM
  • i was having the same problem.

    now it is solved as your suggestion.

    Thanks.

    Ramkumar

    http://www.sanroniks.com

    Monday, July 19, 2010 10:17 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.

    Please help!

    Tuesday, October 19, 2010 2:58 AM
  • Thanks, it worked for me. Damn this Troelsen with not working examples.
    Sunday, November 07, 2010 7:27 PM
  • Thanks for the help!
    Thursday, December 08, 2011 5:17 PM