Jacob Wilkins (5/24/2015)
When you say you couldn't attach it, what was the exact error you were getting?Also, just so I'm clear, when connected via Windows Authentication, you could attach it from a different path than
C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA, but when logged in as sa, you couldn't attach from that different path, and had to move it to C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA?
Cheers!
1. Error is :
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Unable to open the physical file "D:\Download\2015\AdventureWorks2012_Data.mdf". Operating system error 5: "5(Access is denied.)". (Microsoft SQL Server, Error: 5120)
2. I can do it if I am connected via Windows Authentication from any path.
I can do it with 'sa' also, but I have to copy mdf file to C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA .
I hope I was clear this time.
I found two answers on net about similar error :
http://dba.stackexchange.com/questions/46491/permissions-needed-to-attach-adventureworks-database
I hope I don't break any rules on forum because of links. I am just very curious about solution.
Solution on one of them was just copy file to install location but for the sake of learning I want to know why. That isn't solution I hoped for, that is just putting problem away. I don't like to leave things unknown when I suppose to learn as much as I could. As I said before I should for example disable Windows Authentication but how when I cannot do basic stuff with 'sa' user.