Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am trying to attach to database file (*.mdf, *.ldf) that I placed in the same folder as all my other SQL Server databases. I begin the attach by attempting to browse to the folder which contains the db files as well as all of my active database files. I select "attach Database" and click the "Add" button to add a database to the list of databases to attach to. When I do so, I get this error:

TITLE: Locate Database Files - BESI-CHAD
------------------------------

D:\SQLdata\MSSQL10_50.SQLBESI\MSSQL\DATA
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.  

If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Locate dialog box.

------------------------------
BUTTONS:

OK
------------------------------

The path is correct and, as I mentioned, it contains all of my other database files so I wouldn't think that permissions should be an issue, but here is what I see for that folder:

alt text

Any idea why I cannot browse to that folder and attach to the db files that I have place there?

share|improve this question
    
out on a limb here, but look at the acls on the actual file! –  user33788 Jun 4 '10 at 14:06
    
I get the error before I even locate the file. The folder is not even visible to SQL Mgt Studio –  Chad Jun 5 '10 at 1:17

5 Answers 5

up vote 4 down vote accepted

The Service of your SQL Server Instance hasn't enough rights to read the Data-Directory and/or the path of the folder. (Including the root of the drive to list the tree in the dialog box.) The Service needs at least the rights for listing the content of the whole path.

If this is just a testing/developing environment the best way to solve this is to configure the SQL Server Instance Service for running with the SYSTEM-Accont. The SYSTEM-Account has Access to the Root of the Drive and the Data-Folder. This eliminates also a lot of other problems.

If you are using Windows Authentication you also need to run the Management Studio as an Administrator, if you don't have enough rights on this folder.

Warning: NEVER use the SYSTEM-Account for a productive Server. Use the Exec-Command to attach Databases and ensure there are enough rights for the service to access the Data-Folder.

share|improve this answer

The user should have full access to the files, but in my opinion, attach the database while using 'sa' or another SQL Administrator, and then your database users will be able to access the databases.

share|improve this answer
    
This is totally wrong. See DiableNoir's answer. –  deerchao Jul 6 '12 at 8:01

It's clearly a rights issue, because when I run SQL Server under the "sa" account I have no problem.

OK...So, how do I fix it the right way? Which rights should I give to what user, assuming a default setup?

share|improve this answer

DiableNoir's answer seems to be the right one. However, I couldn't quite understand what he meant until I read another post here.

The short and practical answer would be:

Go to Computer Management > SQL Server > Properties > Logon > Here you have to check the mark on Local System account > Click on Allow service to interact with Desktop (Sheela Chauhan's answer paraphrased).

WARNING: Just keep in mind the warning by DiableNoir.

share|improve this answer

You much add USERS group in the security of the source/target folder of backup.

To do this, right-click on the folder, select computer name, ... and add USERS.

share|improve this answer
    
Welcome to SuperUser. When you type out ... it doesn't really explain what's happening. Would you mind going back and listing the steps and/or commands so that they are clear to the reader? –  Raystafarian Jul 27 '13 at 9:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.