Permissions error when attaching a database: "Cannot access the specified path"

Question

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!

Quickman

Why don't you move files to a more suitable drive and folder? I think in this way you wouldn't have any problem. – Ingenerate

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 – Groundsheet

Answer 1

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.

Duress

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

@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? – Duress

Answer 2

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

Zalea

Answer 3

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.

Leaves

Much better to import using the default "sa" user account. – Marcille

This was exactly my issue – Hypochondriac

Answer 4

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.

Butyraldehyde

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 – Issuable

Answer 5

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.

Protostele

Answer 6

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.

Changeless

Answer 7

Ex: C:\Users\abdul\Downloads\Sample Databases

Step 1 - Open SQL Server 2022 Configuration Manager, head to properties of "SQL Server (MSSQLSERVER)" and copy the Account Name. (Ex: NT Service\MSSQLSERVER)

Step 2 - Head to the highest folder (hierarchy-wise) in the path after "C:\Users" and edit the security dialog box.(Ex: Edit the folder named "abdul")

Step 3 - Add a security profile by clicking on "EDIT" permissions and then click on "ADD".

Step 4 - Paste the Account Name from Step 1 and press OK.

Step 5 - Check the "Full Control" box and click OK.

Now restart SSMS and try accessing the path.

Capable

Answer 8

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

Brickbat