20

I am running SQL Server 2012 Enterprise Edition. I have downloaded the AdventureWorks 2012 OLTP data files (both .mdf and .ldf). I saved them to the AW folder that I created in My Documents where I unzipped the file. I go to SQL Server Management Studio (SSMS) right-click on database, and click attach in the context menu. In the dialog I click find.

The UI does not allow me to navigate to folder. I try typing the full path to the file, and then I get the following error:

Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.

I closed SSMS and restarted it, this time running as the administrator. Same bad result.

This should not be this hard. What are the exact steps to successfully attach the AdventureWorks2012 database to SSMS?

Thanks!

user2151027
  • 203
  • 1
  • 3
  • 5
  • Why don't you move files to a more suitable drive and folder? I think in this way you wouldn't have any problem. – Maryam Arshi Mar 09 '13 at 08:20
  • Is your SQL Server instance running on your own machine? If not: the file must be **on the server machine's** file system - not your own – marc_s Mar 09 '13 at 08:49

7 Answers7

20

Connecting to the server by Windows Authentication worked for me. I don't know if that is possible in your case.

Rogitel
  • 211
  • 2
  • 7
14

As Rogitel says about, this could be related to how you have logged in to SSMS itself, rather than the privilege level of the application.

It seems as though, if your user does not have the "sysadmin" role, then they will not have sufficient access to the filesystem on the machine in order to open the file browser.

Setting aside the security concerns for one moment....

In order to grant this role;

  1. Log in to SSMS using Windows Authentication
  2. Navigate to Security->Logins
  3. Either double Click, or Right Click and select Properties on the user you're interested in .
  4. In the "Select a page" box, select "Server Roles"
  5. Make sure that the "sysadmin" role is selected
  6. Press Ok to save the change and close the dialog
  7. Exit SSMS
  8. Reopen SSMS and login as the User you wanted, and you should now have sufficient Privileges to browse.
PGallagher
  • 3,123
  • 2
  • 26
  • 37
10

Put your files to default location for Database Engine data files. If you didn't reconfigure anything it should be 

\Program Files\Microsoft SQL Server\MSSQL11.<InstanceID>\MSSQL\Data

And then Attach db using SSMS.

peterm
  • 91,357
  • 15
  • 148
  • 157
  • 1
    Even though this is a workaround to enable SQL to access the files, it doesn't solve the issue of SQL management studio not being able to access a certain path. I have tried given permissions to all users to the specified folder on Windows 10 with no luck. – Oscar Ortiz Jul 31 '19 at 15:58
  • @OscarOrtiz The fact that you can't it make it work makes the answer less useful for everybody who it worked for and is the reason for the downvote? – peterm Jul 31 '19 at 16:11
9

It doesn't matter what permissions the account that you open the management studio has, the important account is the one that the sql server service is running as. By default it only has access to the default location specified during the install, so you can either do as the previous poster said and move your files to there, or add the service account to the permissions of the folder where you want to store your files so that it can access that location.

steoleary
  • 8,968
  • 2
  • 33
  • 47
  • Also note that the SERVICE account must have permissions on all the parent folders as well else the file's parent folder won't show up in SSMS. Once you are done adding the permissions you can refresh SSMS – Gilbert Jul 02 '21 at 08:06
0

In my case it was related to the drives not being dependencies of the SQL Server role (Clustered Server). When I added the disks as dependencies under the SQL Server role, I could browse the drives from SSMS again.

user2854725
  • 1
  • 1
0

Connecting to the SQL server using the 'sa' user did the trick for me. Not everyone can have permission to do that so make sure the user you are using for SQL Authentication has all necessary privileges.

Saumil
  • 2,521
  • 5
  • 32
  • 54
-3

Just go to service in your computer and search for MSSQLSERVER restart the service by stop and start.

bummi
  • 27,123
  • 14
  • 62
  • 101
user5766120
  • 1