Take the 2-minute tour ×
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 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?

share|improve this question
    
That is weird. What are you doing with the files after you detach a database? –  DForck42 Oct 12 '11 at 18:29
    
It depends on why it was detached. I have a silly app that uses many, many databases and detaches them after they've been inactive for a long time to save on load. Other jobs need to come in later on and do things with it like move it to a different folder or delete it. (to preempt the inevitable: I understand the problems involved in detaching databases, I understand it's not ideal, etc. I want to know if it's possible to retain file permissions when detaching) –  sh-beta Oct 12 '11 at 19:41
    
set it as offline rather than detaching it? –  JamesRyan Oct 13 '11 at 11:39
    
So it seems the answer is no. Who wants the points? –  sh-beta Oct 16 '11 at 5:14
    
if you find definitave proof that it's not possible, post it as an answer and then accept it. –  DForck42 Oct 21 '11 at 19:00

2 Answers 2

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

Your Answer

 
discard

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.