SQL Server 2008 database copy - file permissions

Question

For SQL Server 2008 Developer Edition on Vista 64 bit:

I tried copying a database using a Vista admin account using the attach/detach method and it failed due to a file permission error so I gave the user that sql services are running as write and modify to the directory. The copy didn't work. I then gave it full control. The copy worked.

Does that make sense?

If I revoke full control from the user, will that cause problems?

The weird thing is that in an existing working database with files in a different directory, there are no special permissions on the directory and files for the database, so why does a copy require full control?

Answer 1

When you detach a DB, the MDF/LDF files may be set with more restricted perms than you expect, like exclusive to the principal that did the detach - maybe the SQL Server service account or the domain account of the user that performed the detach. I have in the past had to manually add back permissions on the files' Properties > Security tab for other users, or else the files act as if they are locked. See also http://www.onupdatecascade.com/2009/07/sql-server-locks-mdf-and-ldf-files-upon.html

also: http://msdn.microsoft.com/en-us/library/ms189128.aspx

( thanks GrumpyOldDBA )