When a user detaches a database from MS SQL Server, it sets the owner of the data and log files to that user. It also removes all non-owner permissions from the files. This is partially explained at http://msdn.microsoft.com/en-us/library/ms189128.aspx

I have tested this on the following versions of SQL Server

  • 2005
  • 2008
  • 2008 R2

Is it possible to change this behavior so file permissions are retained when a database is detached?

  • set it as offline rather than detaching it?
    – JamesRyan
    Commented Oct 13, 2011 at 11:39

3 Answers 3


While not a concrete answer, I find no configuration option or setting to override this behavior. It seems to be baked in.

Everywhere Detaching and Attaching Databases is mentioned in the documentation points back to the Securing Data and Log Files page you referred to above. The scenario on this page makes it pretty clear what is going on, and why the files should be secured as such.

Perhaps you could use a different user or service account to perform the operation? IF the file is owned by the proper account (such as the SQL Server service account) this whole thing may be a non-issue.


By design it is not possible to change this behavior through SQL Server Management Studio (SSMS). There are documents from Microsoft (here and here) that “clearly” explain this behavior. While the articles are related to MSSQL 2008 is still true for MSSQL 2005/2012 (needs to verify for 2014): “File access permissions are set during a number of database operations, including detaching or attaching a database. For information about file permissions that are set whenever a database is detached and attached, see Securing Data and Log Files in SQL Server 2008 R2 Books Online.” second link from above.

One possible work around to resolve this situations is:

  • Create a batch file to fix file permissions.

Using ICACLS you can execute something like this:

    icacls c:\data\* /grant:r “OWNER RIGHTS”:FMRX
    icacls c:\data\* /grant:r Administrators:FMRX
    icacls c:\data\* /grant:r “NT SERVICE\MSSQLSERVER”:FMRX

I do like the answer given on a similar question:

Enabling startup trace flag 1802 will allow SQL Server to retain inheritable permissions when a database is detached.

Obviously, I think the preferred DBA method to do this is simply to backup and restore to a different DB. But detach-copy-reattach is only one long file operation rather than 2 long file operations (backup, restore).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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