When trying to attach the database to SQL Server with an account other then yours you might get following error.
TITLE: Microsoft SQL Server Management Studio
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘D:\MSSQL10.MSSQLSERVER\MSSQL\DATA\DBName.mdf’. (Microsoft SQL Server, Error: 5123)
If you follow the link it explains nothing; a little confused I did search on the Google (yeeh?). With no answers as to why this is happening, I made sure SQL Server had proper permissions to the MSSQL Data and Log folders. I decided to run Process Monitor to see what service account is trying to access the file when I am attaching it; it was as expected my SQL Server Service account was trying to access the file. So why the permission denied? File was located in same folder as rest of mdf/ldf files…
Looking into it I saw the file had a small lock icon on it like ““. Looking into the permissions on the file I see it was only accessible by my user name. I don’t know why this was the case (actually I found why, I’ll post on it tomorrow; need to do a bit more research on that topic). I edited the permission properties on the file to inhert form parent folder.
Reattached the database with no issues.
Issue: Can’t attach mdf and ldf file to the SQL Server, keeps getting permissions deined even though file is in default data/log directory and SQL Server has access to it.
Solution: Check file to make sure it is inheriting permissions from parent and is not owned by single user.