For example, when installing SQL Server 2008, I had to pick an account for the database engine to run as.

When I went to attach a 2005 database file with SQL Server Management Studio, there was a permission error preventing the database upgrade. I checked the database files, and the "SQLServerMSSQLUser$ComputerName$MSSQLSERVER" account has full permissions on the file.

So I figured that SQL Server Management Studio was running under my username, which did not have write permissions to upgrade the database file. So, I added full permissions for my username, and then it worked.

This incident is what led me to ask this question. How can I know for certain which account an action is running under? I thought the database engine would be handling attaching a database, but apparently not!

Furthermore, once the database was attached, it seems to have removed my username from the security list! So I imagine that if I detach the database, I'll have to reset my permissions on the files once again before I can re-attach the database.

SSMS gets the filename fine, so wouldn't it be the TSQL that's failing? The NTFS file permission is definitely a problem, since giving myself write access to the file fixes it. Interestingly, when detaching the file, it clears all NTFS file permissions and users, adds just my username, and grants me full-access permissions to the file, which allows reattaching. Interestingly, if I give myself read-only permission to the file once again, the database attaches as read-only; and that only works now because the initial upgrade was already completed, so write permission is no longer needed.
–
TriynkoJul 13 '09 at 18:14

Since it totally takes control over the file permissions during attach/detach, there are probably documented expectations for file permissions when performing that action in SQL Server 2008 that I just haven't encountered... and as I'm writing this I found the answer on Experts Exchange, so I'll post it here, see my answer.
–
TriynkoJul 13 '09 at 18:33

sqlservr.exe process (running MSSQLSERVER service) is running as SYSTEM. ssms.exe is running as me. So, I know which username is associated with which process. What I'm asking is how do I know which process is performing which action. I though the database engine (running as SYSTEM) would be the one actually accessing the file, yet my username seems to need read/write access to it for it to complete. Why?
–
TriynkoJul 13 '09 at 18:20

I voted this one up, since it's somewhat useful/relevant to this problem. Who would have guessed that the service, which is setup to run as SYSTEM, actually deems it necessary to impersonate my account to attach the database! Strange, but it's all there in the documentation.
–
TriynkoJul 13 '09 at 18:46

Relevant SQL Server Documentation is here:http://msdn2.microsoft.com/en-us/library/ms189128.aspx
"When you are detaching or attaching a database, the Database Engine tries to impersonate the Windows account of the connection performing the operation to guarantee that the account has permission to access the database and log files. For mixed security accounts that use SQL Server logins, the impersonation might fail."

Also, it clearly states: "File access permissions are set during any of the following database operations: creating, attaching, detaching, modifying to add a new file, backing up, or restoring."