Attach databases in SQL 2008.

Question

I have recently noticed that If a user detaches a database using SQL 2008, no other user can attach the database back apart from the original user who actually detached in the first place. Can someone please confirm this behaviour in SQL 2008.

I currently get an error message

Reason 1815 : File activation failure

Answer 1

Could you try placing the file in the default SQL Server DATA folder and trying again?

-Sean

Answer 2

Sorry, I havn't tried that yet, but in the meantime, what I done was to attach the files back using thesame login which detach, what actually led to the problem was the fact that I couldnt copy files from detached databases. So what I done was to re-attach back using thesame accounr that detached.

detached with thesame account as the prev paragraph
then I was able to copy the files using thesame windows account.

I just dont understand, SQL 2008 definitely changed.

Answer 3

This sounds like a windows permissions issue. Adding the SQL Server windows group to the folder in the permissions tab should also fix this.

-Sean

Answer 4

The change in file permissions upon detaching a database is by design.  It's a security feature introduced in SQL Server 2005.  File access permissions are set during any of the following database operations: creating, attaching, detaching, modifying to add a new file, backing up, or restoring.  For more information, see http://msdn.microsoft.com/en-us/library/ms189128.aspx (Securing Data and Log Files).

Kind Regards,
Gail

---

Gail Erickson [MS] This posting is provided "AS IS" with no warranties, and confers no rights