Can't gain access to my database created though Visual Studio?

Question

I have created a database emailDatabase, its stored in

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA

In Visual Studio, I use the Server Explorer, click the add new connection button.

The add connection dialog box appears.

Under server name I use the dropdown box and select DEV-5\SQLEXPRESS. I use Windows authentication.

In the Connect to section at the bottom, the dropdown displays: Master, Model, msdb and tempdb and does not display my emailDatabase.

So I select Attach Database File and click browse and follow

local C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA

and select my email database.

The following error occurs :

emailDatabase
You don not have permission to open this file.
Contact file owner or an administrator to obtain permission.

I think my problem is i saved my database wrong, I need to make a back up or something like that. if that's the case please tel me how to make a backup and so on. I really need to move forward from this problem.

When I created my database I right-clicked on databases in SQL Server Management Studio and said new database, then I added columns with a query. then file save all.

How can I get a copy of my database file with all the permissions I need to use it in visual Studio??

Solution 1

When you create a database on the server (using SQL Server Management Studio), you don't have to (and should not!) fiddle around with the database file(s) anymore - let the server handle that for you.

Instead: do a Add Connection in Visual Studio and then specify the server instance (DEV-5\SQLEXPRESS) and the database name (emailDatabase) in your connection dialog.

With this, you're connecting and using a SQL Server database the way it's intended to be used - on the SQL Server instance itself. This is much easier, and much less hassle, than having to struggle with "free-floating" .mdf files and attaching them to your solutions and stuff like that....

So here - fill in DEV-5\SQLEXPRESS into your "Server name" dropdown, and then use the "Select or enter database name" option and enter your database name (or pick it from the dropdown) - it should be there!

DO NOT use the "Attach a database file" option - this is the free-floating .mdf "feature" which is rather clumsy and hard to use and error-prone - again: I recommend not using that...

Solution 2

Had the same problem and I realised the problem was not in VS2010 but my SQLserver.
My instance name is OMAFANO ,and that's what my MSSQL connected to under Server Name. Now here's the catch,click on that and connect to OMAFANO\SQLEXPRESS and create all your databases and tables there if you want them to show up in VS2010 the way u stated up there. So under server name in VS2010 also write INSTANCENAME\SQLEXPRESS if you want to see your newly created databases etc. Take a look at the picture:

Comments

[Pomster]

I have not touched any of the files, i created my Database in MS SQL Server then clicked save all, and now i'm trying to locate it in Visual Studio.

[Pomster]

Yes i get to that screen and have it like yours, but in the select or enter a database name, mine is not in the drop down? in my question i mention that only the master, Model, msdb and tempdb are there.

[Pomster]

What can i do, or what didn't i do to get my database to be in that drop down box??

[Pomster]

I have Microsoft SQL Server Management Studio 2008 full version, when i start it the connect to server dialog box appears: Server type = Database engine, Server name = (local), Athentication = windows, then i click connect and i can view my databases. i have system databases with master, Model, msdb and tempdb.and then a folder after that that says email database. so its there

[marc_s]

@Pomster: if you connect to (local), then you create your database on the default instance on your machine. In that case, you need to also input (local) into the "Server name:" dropdown box in the Visual Studio dialog to connect to that instance where you created your database! You definitely have two instances - the default ((local)) and that Express (DEV-5\SQLEXPRESS) - this is like two separate SQL Servers alltogether - you need to be aware where you create your database - you cannot pick it from the other instance, obviously....

[Pomster]

Oh ok thank you very much, at least i know where i went wrong, thank you, you have been a great help, so i should just put local into Visual studio and i should pick it up?

[SendETHToThisAddress]

Thank you this was my exact problem! I suggest adding an edit to your post that if you already have SQL Server open and you're connected, you can also see this connection string at the top of the object explorer, that was how I found it.