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

1 Answer 1

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

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.