February 27, 2007 at 2:23 am
Hi
My software uses varous accounts to access the databases and attach and dettach as required, this works fine for SQl 2000, however the SQL Server 2005 Database Engine sets file access permissions on the physical data and log files of each database to specific accounts each time the database is attached and then detached. All the NTFS accounts on the physical mdf and log file are deleted apart from the user whom has detached the database. The article here explains what is happening http://msdn2.microsoft.com/en-us/library/ms189128.aspx this is dated dec 2005, does anyone know if there is a way of turning this behaviour off or overiding it, or if it has been referenced in SP1 or 2?
Thanks in advance
Leo
March 2, 2007 at 8:00 am
This was removed by the editor as SPAM
July 3, 2007 at 1:56 am
I am having SP2 and tried the same issue persists. After detaching and attaching again the permissions are given only for the user attaching the database and the local administrators group.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 3, 2007 at 10:20 am
There is a hotfix for SP1 that allowed a change in behavior and this hotfix should be rolled into SP2. However, it requires SQL Server to be started with a trace flag. Keep in mind this only affects attach/detach. Newly created databases still get the permissions changed, whether we like it or not.
K. Brian Kelley
@kbriankelley
July 4, 2007 at 1:53 am
Brain,
Can we have the hotfix number. I still face the same issue with SP2. Might be i dint set the trace flag as you mentioned. Can you let me know the trace flag too.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 4, 2007 at 1:29 pm
Sugesh Kumar, the link I provided goes to the Microsoft KB article. I have also included the KB article # in parentheses (922804) as a reference as well.
K. Brian Kelley
@kbriankelley
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply