Access Denied Error attaching an existing MDF

I guess I messed up somehow, but I am not sure how to fix this.

On Vista, with SQL Server 2005 Developer Edition, I created a 6GB database and loaded it up with data. I then decided that I wanted to burn a copy of the files, so I detached (no problem.) But when I wanted to copy the physical MDF and LOG file to another folder. Vista said I did not have access to the folder, but if I hit continue, no problem--it would correct the situation. I did. I copied the files (no problem), but now when I go to reattach to the MDF (that is still in the Data folder), I get a "CREATE FILE encountered operating system 5 (Access is denied)" error. I checked permissions and SQL Server has full rights. I am the only user on the system (Admin), and the files do not appear to have anything weird set (read only, advanced permissions, etc.)

What did I do wrong? And how do I fix this? Oh the joys of advanced Vista security features Even the admin is locked out now.

Thank you for your help!

Michael

Usually the difference for Vista is that you do not get access simply by being a member of the Administrators group. You would need to grant explicit access to the account for the SQL service as well as your own account.|||

I want to understand what you are saying. Are you saying the error is occuring because my login was added to the permissions on the Data folder? MSSQLSERVER has full permissions to the folder and file. That doesn't appear to have changed. Or are you saying that I need to add permissions back for another service like the Agent?

I am trying to understand what Vista did that makes this operation fail.

Thanks!

|||

Attach will attempt to verify that your account has access to the file before attaching it to the server. This keeps random users from attaching random files that they would not normally have access to.

Check the file permissions for both the service account and your account.

|||

Check the NTFS permissions on the actual files. I have see time where after detaching a database from the SQL Server the only user account which has access to the files is the user who detached the database. All other accounts are removed including the SQL Server.

It can be a deceving problem becuase the NTFS permissions on the folder look fine (because they are), but the NTFS permissions of the data files are screwed up.

|||

I will check that out when I get in front of that PC -- thanks! I am still baffled by it. I was able to copy the file to another folder and attach, so I was able to move on. However, the copy that was in the original location still has the issue.

Thank you for replying -- that actually makes sense.

Michael

|||Permission on detach works differently in sql2k5 than sql2k (i.e. it does not preserve all acls like sql2k). This has caught many sql2k users off guard.
http://msdn2.microsoft.com/en-us/library/ms189128.aspx