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.
Best Answer
Looks like SSMS is causing this confusion. My guess is that having to select the files in a dialog box means that the account SSMS is running under also needs permissions to the file.
As long as the service account has permission to the file, then you should be able to attach the database using t-sql.
Try using the sp_ attach _db You'll need to login as sa or use setuser or use the CREATE DATABASE... FOR ATTACH
If you really must use SSMS & the thought of T-SQL makes you want to throw-up then create a user account for the sql server service to run under & then use this account to start SSMS.