locked
Attach databases in SQL 2008. RRS feed

  • 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
    Thursday, December 10, 2009 1:47 PM

Answers

  • 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
    Thursday, December 10, 2009 4:44 PM

All replies

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

    -Sean

    Thursday, December 10, 2009 2:38 PM
    Answerer
  • 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.


    Thursday, December 10, 2009 2:49 PM
  • 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
    Thursday, December 10, 2009 2:52 PM
    Answerer
  • 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
    Thursday, December 10, 2009 4:44 PM