Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

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?

share|improve this question
set it as offline rather than detaching it? –  JamesRyan Oct 13 '11 at 11:39

3 Answers 3

up vote 6 down vote accepted

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.

share|improve this answer

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
share|improve this answer

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).

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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