Detach Dbase on SAN with SQL2005

I'm not sure if this is the right place for this but I have an issue.

I have a SQL2005 server with SAN drives for the MDF/LDF files. I have updated recently to SP2.

When I detach a Dbase using the appropriate command it strips the Security on the file level (which SP2 is supposed to fix) and I am unable to attach the database.

I am using a domain account to do this with from the query analyser window.

Anyone have any thoughts on this?

There was no change to the file level security done for detach/attach in SP2. This is by design. Why do you think something was supposed to change?

One thing to look at is why you are doing detach/attach instead of OFFLINE/ONLINE if you are going to reattach to the same instance.

|||

With SAN attached drives, when you detach a Dbase the file level security changes. If using the same account used to execute the script with xpcmdshell you cannot rename the file before attaching. When SP2 is applied it allows you to reattach the file and fixes the security at the same time. Because the security is changed when detaching you have to add security to the file before you can rename it within the same SQL script before attaching.

I was under the impression that the hot fix included in SP2 would not strip all of the secuirty off the files, as a result we have to change the script that is running to add a group to the file before renaming it. I found a work around to our problem, but thanks

|||

There was a change to file permissions done under NAS that was done in SP1 and in a QFE. It is discussed in this KB article.

http://support.microsoft.com/default.aspx/kb/922804

Unfortunately, I do not think the article is complete as the fix is just to add a traceflag to revert to SQL 2000 behavior but the article does not actually mention that. You need traceflag 1802 for this behavior.

|||

Thanks, I got that one earlier. Before this hot fix when you detach the security changes and then you cannot reattach.

When you apply this hotfix, and flag, you can detach and reattach with no issues as the security get "reset" when you reattach.

My issue was that I was running an in house script that would detach, rename the physical files and then reattach with the new name. With the security changed it cannot change the file name.

Workaround i have had to add a line of code to add the local admin group to the file permission before the file rename.

Works for me. Don;t think MS was thinking about this use.