Permissions error when attaching a database: “Cann

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!

Answer 1

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.