CODEWITHSUNDEEP
sql-serverdatabasevisual-studiovisual-studio-2010sql-server-2008
Pomster
Pomster

Reputation: 15197

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

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??

Upvotes: 10

Views: 34738

Answers (6)

lashima
lashima

Reputation: 1

I had the same issue, you just type your local server name "sara-PC" instead of "sara-PC\SQLEXPRESS"

Now you can access your database easily, you can see it in your dropdownlist. And also please dont use file access method to attatch database, thats not good way. Also you can put ~....\Data and ~...\log file wherever you want by setting default location using server->rightclick->properties->Database settings.

Definitely this solves your issue.

Upvotes: 0

Emfocuser
Emfocuser

Reputation: 1

To connect to a ssms2014 database from visual studio 2013, in the new connection wizard I had to change 'data source' from 'ms sql server database file' to '.net framework data provider for sql server..'. Then I was able to enter [computer name][username] for windows authentication.

Upvotes: 0

Dala
Dala

Reputation: 1

Run Your Visual studio As Administrator Go to Visual Studio instance i.e C#,C++ etc Right click > Run as Administrator

Then now It may work

Upvotes: 0

Manny265
Manny265

Reputation: 1709

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:
enter image description here

Upvotes: 1

GDB
GDB

Reputation: 3579

I thought I had it figured out but problems continue to pop up. So ...

IGNORE EVERYTHING BELOW THIS LINE.

After hours of tinkering I finally figured out how to use SSMS to connect to a SQLServer 2008 database that was created in VS2010, and it is relatively simple. But from the number of unanswered questions all over the place not well documented. Here's how to do it:

  1. In VS right click the project in SolutionExplorer and select Add new item then select Data then SQLServer database. It will prompt you to save it in the app_data folder and create the folder for you if it doesn't exist.

  2. Find the Ssms.exe file (on my system it's in C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE

  3. Right click Ssms.exe and select Run as administrator.

  4. After SSMS opens you can attach the project MDF. It will now be on your list of databases. The database name is a GUID.

  5. You can even run aspnet_regsql against the database to add the Membership tables and sprocs.

IMPORTANT! You can manage this database with EITHER SMSS OR VS but not both at the same time. If you mess with the database from within VS using ServerExplorer you will break the SSMS connection. To fix that you will need to detach and reattach the database.

Upvotes: 0

marc_s
marc_s

Reputation: 754220

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....

enter image description here

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...

Upvotes: 17

Related Questions