Recently, a problem was presented to me by a DBA using SQL Server 2008 R2. He could not attach a database whose fileshad been copied from another server. This blog talks about the situation and the solution.
Here are the steps he went through to arrive at his situation.
Other pertinent information:
My first thought is that because both DBAs are local admins and sysadmins the attach database command should succeed. A quickcheck of Books on Line (BOL) brings this explanation of why it did not work: http://msdn.microsoft.com/en-us/library/ms189128.aspx.
The reason this did not work initially is because two different people did the detach and attach operations as explained in the BOL link. So the files were owned by DBA 1. However, DBA 2 was a local administrator so he had access to the directorywhere the files were stored, so he could copy the files. But the attach failed on server 2 because DBA 2 was not the file owner.
To get this to work, we went into the copied file properties for the .mdf and .ldf files and changed them to be owned by the local SQL Server service account. Then the attach worked. It would also have worked to change the file owner to DBA 2, the one issuing the attach command.
To test the scenario where the same person does the entire process, we created a sample database on server 1 and used the same person to detach, copy and attach the databases. The same servers involved in the original problem were used. This time the attach operation worked.
We noticed something interesting during this test. After creating the sample database, the mdf and ldf files (while still attached) are owned by the SQL Server service account. Use Windows Explorer and properties of the file to see the owner. Then we detach the files, the owner becomes the person who did the detach command. After copying the files to server 2, the ownership remains the same; i.e. the person who issued the detach command. After the attach command is issued on server 2, the file owner becomes the new SQL Server service account.
Impersonation is important
This test worked because the same person did the entire process. Whether you use SQL authentication or Windows authentication to log into the database via SSMS, the process works as long as the same person does the entire process and as long as the connecting account can be impersonated.
File ownership can be a bit confusing. It makes a difference whether the two servers are in the same or different domains and it makes a difference if the same person does the entire process. It all comes down to knowing whether youraccount is being impersonated on either server involved.
Author: Kevin Cox (SQLCAT), Cindy Gross (Microsoft Premier Field Engineering)
Contributors: Chuck Spencer (Pearson/eCollege)
Reviewers: Sanjay Mishra, Shawn Tinline-Jones, James Podgorski
I just rant into a similar NTFS problem with SQL Server 2012 and it goes without saying that granting permissions and/or ownership to the 'NT SERVICE\MSSQL$SQLEXPRESS' fixed things right on up.