Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have an ASP.NET MVC web application deployed to IIS on a Windows Server 2012 machine. When the application first starts, it will create the database and tables it needs if necessary. This all works fine, and now I want to do some administration to the database through SQL Server Management Studio. However, when I open up that tool I only see the System Databases. What do I need to do in order to view the database created by the web application?

Some details that may help:

The application uses the following connection string in Web.config:

<add name="DefaultConnection" connectionString="Data Source=.\SqlExpress; Initial Catalog=Foo.Dashboard;Integrated Security=SSPI;User Instance=true" providerName="System.Data.SqlClient" />

In IIS, the application pool is set to run with the identity LocalService and the Load User Profile setting is True. On Sql Server Express, we added a login for the Local Service account and gave it the dbcreator role. When the application started up, it was then able to create the database and tables, and everything appears to be functioning.

I then connect to the server with remote desktop and open SQL Server Management Studio. To connect to the database, I enter .\SQLEXPRESS as the server name and authenticate with Windows Authentication. I then connect and only see the System Databases. I’ve added all the possible database roles to my login, and I see that I have the VIEW ANY DATABASE permission in the Effective tab.

share|improve this question

1 Answer 1

up vote 2 down vote accepted
User Instance=true

It's because it's running as a user instance. You might be able to connect to it using the details here

Alternatively, if you remove the User Instance=true from the connection string, if you're using EF code first then this should cause a new DB to be created on the SQL instance proper. Bear in mind that the tables will be empty and you'll need to move any data over from your user instance, assuming there is any.

share|improve this answer
    
Removing User Instance=true worked. Thanks! –  elevine Dec 13 '13 at 12:29
    
One thing that confuses me, if I login to Management Studio with an account that has been given all roles, I would expect it to be able to view and manage all databases. Why is that not the case? –  elevine Dec 13 '13 at 12:37
    
Because when you fire up Management Studio and connect to .\SQLEXPRESS, you're looking at the machine instance. User instance DBs will not appear here. –  Chris McKeown Dec 13 '13 at 12:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.