When you detach a database from Microsoft SQL (I’m talking SQL Server 2005 and up here) the SQL Server automatically changes the NTFS permissions on the file so that only the user who told the SQL Server to detach the file has access to them. SQL Server does this to ensure that an unauthorized person isn’t able to access the data files.
However the downside to this security measure is that the SQL Server also doesn’t have access to the files when you try to attach the files back to the server, which will cause SQL Server to throw a permissions error when attaching the database weather using SQL Server Management Studio or T/SQL. Because of this you’ll need to edit the NTFS permissions for all the files on the SQL Server and give the SQL Server service full control rights to the database files.
Denny
One Response
This one caught me by surprise just a couple of weeks ago. Took me damn near an hour to figure it out.