Ian Kirk - SQL DBA

Continuing a bit of a theme in my recent posts, I ran into an item today that caused me to pause and make sure I’d shared it with the rest of the DBA team - something I’m trying to be a little better about doing, as sometimes I’ll just drop my head and go headlong into a task.  Now, not only will I make sure those around me know what I know, I’ll go ahead and make a mental note to share with everyone else, too.

I initially encountered this “problem” at 3am in the middle of a high visibility database re-host where I had to relocate the data files from one server to another using a detach / attach process.  After detaching the database through Management Studio, I remoted into the server to copy the file, only to receive the error “Access is denied. You don’t have permissions or the file is in use.”  I thought to myself, of course I have permissions - I’m a local administrator on this server.  Next, I thought why would the file be in use - I just detached it!

After about 30 minutes of chasing our tails, we discovered that with SQL Server 2005, when you detach a database, the file system object will have its NTFS security changed so that only the Windows account you used to detach the database will have any access to the file.  This becomes an issue for my team because we log into the SQL engine through SSMS using one account, but when using remote desktop, we use a different individual account.

The fix, so long as you are a local administrator, is to right click on the file(s) and alter the security to re-grant the appropriate (likely administrators) Windows group(s) full access to the file.  At that point, you’ll be able to perform the necessary actions like deleting, copying or moving the file(s).

I can’t say I have any idea what happens when you do this using SQL authentication, nor can I speak to whether or not this occurs in SQL Server 2008.

Anyone else experience this one?  Does anyone know about SQL authentication?