none
Can't attach my project database (.mdf file) using SQL Server Management Studio R2

    Question

  • Hi.  I've encountered a strange issue in SQL Server Management Studio (not the express version).  I had to uninstall SQL Server 2008 Developer in order to install SQL Server 2008 R2 Developer.  As a result of doing this, the database from one of my ASP.NET MVC projects is not attached to the database server.  I see the ".mdf" file in my project's AppData folder in Windows Explorer, but I can't navigate to it in SQL Server Management Studio.  I go to "C:\Users\<my name>" and the attach database interface will not let me go any further.  The folder that I need to access is "C:\Users\<my name>\Documents\Visual Studio 2010\NerdDinner\NerdDinner\App_Data".  How can I access this directory from SQL Server Management Studio?  Is there some sort of strange permission issue?  Thanks in advance for any help.
    • Moved by Figo Fei Tuesday, May 11, 2010 7:36 AM (From:Visual Studio Database Development Tools (Formerly "Database Edition Forum"))
    Tuesday, May 11, 2010 4:25 AM

Answers

  • I had the same problem but was able to solve it during a reinstall but by opening SSMS, in the Object Explorer (where the links are to your dbs, security, server objects, replication, etc are - select Security, then Server Rolls.  Under Sever Rolls, double click on Sever Admin and Add your account in.  (I actually did this during a re-install and not in this screen but this is the area that references my account and therefore the Sever can impersonate me.)  I now have access to all of my files under my user account through SSMS and can freely attach any DB.

    Paul


    Paul
    Thursday, December 16, 2010 8:26 PM

All replies

  • Moved to SQL Server forum for better response.

    Thanks.


    Figo Fei
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Tuesday, May 11, 2010 7:37 AM
  • It could be a rights issue.  The SSMS uses the server's account to look for files to attach, not your own account.  If the SQL Server service account does not have rights to the folder, then it will not see the files.  It is certainly possible for you to have rights that are not granted to the SQL Server service.  But the tools have issues at times, so...

    Can you use a script to ATTACH the database?  Such as:

    CREATE DATABASE [NerdDinnerApp] ON
    ( FILENAME = N'C:\Users\<my name>\Documents\Visual Studio 2010\NerdDinner\NerdDinner\App_Data.mdf'),
    ( FILENAME = N'C:\Users\<my name>\Documents\Visual Studio 2010\NerdDinner\NerdDinner\App_Data.ldf' )
     FOR ATTACH
    GO

    If this works, then you know that the server has the rights.  If it fails, then the tools have been exhonorated and you need to get the rights granted.

    RLF

    • Proposed as answer by crazyv Tuesday, January 04, 2011 6:41 AM
    Thursday, May 13, 2010 2:38 PM
  • Hi.  I've encountered a strange issue in SQL Server Management Studio (not the express version).  I had to uninstall SQL Server 2008 Developer in order to install SQL Server 2008 R2 Developer.  As a result of doing this, the database from one of my ASP.NET MVC projects is not attached to the database server.  I see the ".mdf" file in my project's AppData folder in Windows Explorer, but I can't navigate to it in SQL Server Management Studio.  I go to "C:\Users\<my name>" and the attach database interface will not let me go any further.  The folder that I need to access is "C:\Users\<my name>\Documents\Visual Studio 2010\NerdDinner\NerdDinner\App_Data".  How can I access this directory from SQL Server Management Studio?  Is there some sort of strange permission issue?  Thanks in advance for any help.


    You may be having the same issue that I had. Here is the link that helped me understand.

    http://msdn.microsoft.com/en-us/library/ms189128.aspx

    What is the id that you are logged into in mgmt studio? That would be the id that is trying to attach the DB, but you would have problems if that id does not have access to the mdf file. Go to the mdf file and look at its security.

    Thursday, May 13, 2010 4:47 PM
  • I have never been able to solve this problem.

    When I check the Security tab of the mdf file the login name that is also being used when logging into SQL Server Management Studio is listed as having "Full Control".

    I have had this problem for months now and I would really like to find a resolution.

    Saturday, November 27, 2010 4:31 PM
  • So, the windows ID that is logged into SQL Server (therefore doing the attching) has full control on the MDF file.

    (1) I don't know if this matters, but what about on the LDF file?

    (2) What happens if you log on to the server directly (the machine where the MDF/LFD files are) and try attaching?

    Monday, November 29, 2010 6:42 PM
  • There is no separate server in this case. Everything is installed on the development machine. I think I checked several of the files and they all had the same permissions. However, I will check again as soon as I get a chance.

    Thanks for the response.

    Tuesday, November 30, 2010 6:36 PM
  • The only way I have found to get around this issue is to simply copy and paste the database files from my App Data folder within my project to the default database directory used by SQL Server Management Studio. Then I use Management Studio to perform whatever tasks I need done and then reciprocate the copy and paste procedure back to the App Data folder.
    • Proposed as answer by darrenlc Wednesday, December 15, 2010 3:22 PM
    Wednesday, December 15, 2010 3:21 PM
  • I have the exact same issue except I used Easy transfer to move my files off of my hard drive, upgrade to W7 from Vista (clean install)  and then moved them back and reinstalled all of my software.  I cannot go any father than my user name.  I did work around the problem by moving the db files to a new folder off of c:\ but this is not the type of band-aid solution that I like to use.  There must be a simple solution to this.  I have tried looking at Ownership of the directory and it was listed as system so I changed it to myself but that made no difference.  I have also turned User account control off and ran SSMS as administrator.  None of these solutions worked.
    Paul
    Thursday, December 16, 2010 3:32 AM
  • I had the same problem but was able to solve it during a reinstall but by opening SSMS, in the Object Explorer (where the links are to your dbs, security, server objects, replication, etc are - select Security, then Server Rolls.  Under Sever Rolls, double click on Sever Admin and Add your account in.  (I actually did this during a re-install and not in this screen but this is the area that references my account and therefore the Sever can impersonate me.)  I now have access to all of my files under my user account through SSMS and can freely attach any DB.

    Paul


    Paul
    Thursday, December 16, 2010 8:26 PM