May 24, 2015 at 4:36 am
Fresh install of SQL Server 2012 Express Advanced edition. I downloaded AdventureWorks2012 database but couldn't attach. After couple of hours I attached it when I connected to Management Studio via Windows Authorization, also I found that I can do that with SQL Server Authorization and my 'sa' user but i need to copy AdventureWorks2012 mdf file to my MSSQL Server installed location C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA . Someone suggested on internet to try to open Management Studio as Administrator but that didn't do anything for me.
I understand its about privileges on files and database, I just want to know how to fix it and make it accessible. I didn't found precise advice on this matter.
Sorry if its not on right forum.
Sorry about English, it's not my native language.
May 24, 2015 at 5:02 am
mili.nikolic (5/24/2015)
Fresh install of SQL Server 2012 Express Advanced edition. I downloaded AdventureWorks2012 database but couldn't attach. After couple of hours I attached it when I connected to Management Studio via Windows Authorization, also I found that I can do that with SQL Server Authorization and my 'sa' user but i need to copy AdventureWorks2012 mdf file to my MSSQL Server installed location C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA . Someone suggested on internet to try to open Management Studio as Administrator but that didn't do anything for me.I understand its about privileges on files and database, I just want to know how to fix it and make it accessible. I didn't found precise advice on this matter.
Sorry if its not on right forum.
Sorry about English, it's not my native language.
Normally it is enough to run the SSMS as Administrator (Right click -> Run as Administrator) and then attach the file.
😎
May 24, 2015 at 7:43 am
That isn't precise answer. If anyone knows, how to grant access to attach files for 'sa' user.
I cant use this kind of security on work. If I disable Windows authentication I dont have privileges for attach databases and who knows what else. So I need to know how to resolve this.
Thanks in advance.
May 24, 2015 at 10:22 am
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!
May 24, 2015 at 1:09 pm
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.
May 24, 2015 at 1:28 pm
From https://technet.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.
What is likely happening is that your Windows user has permissions on that D drive path, but the service account for SQL Server does not.
When you run the attach as your Windows authenticated login, SQL Server impersonates your Windows account, which has permissions.
When you run the attach as your SQL authenticated login, it just runs using the service account, which likely doesn't have permissions on that D drive path.
If you give the SQL Server service account permissions on that D drive folder and make sure you set it to replace permissions on the child objects so that the files' permissions also change, then you should be able to do it as the SQL authenticated user as well.
Cheers!
May 25, 2015 at 12:58 am
If you give the SQL Server service account permissions on that D drive folder and make sure you set it to replace permissions on the child objects so that the files' permissions also change , then you should be able to do it as the SQL authenticated user as well.
All your post sounds very logical, I believe that is right answer but I dont know how to do bolded part? Grant SQL Server service account permission for D drive?
May 25, 2015 at 9:40 am
First you would need to figure out what the SQL Server service account is.
For that, just open either Services or SQL Server Configuration Manager, and look at the value listed in the "Log on As" column for the SQL Server service.
Next you would use Windows Explorer to browse to D:\Download\. From there, you would do the following:
Right-click the 2015 folder and select Properties
Go to the Security tab and click Advanced
Click Change Permissions
Check the box for replacing child permissions
Finally, just click Add, and give the service account from the first step full control.
Now, having said all that, do you really want to have live databases in your download folder? It's typically easier to manage the database files if they're in folders just for database files, since you know exactly where everything is.
It also means you don't have to give the SQL Server service account full control on a bunch of folders just because you might decide to put data files there some day, which is more in keeping with the idea of running service accounts with the minimal required permissions.
I'd probably suggest just moving the databases to the Data folder, unless for some reason you absolutely will need to keep data files in the download folder.
Cheers!
May 25, 2015 at 10:16 am
Thank you for very useful post.
Of course I don't want to have database in my Download folder. I understood databases will be in default SQL Server folder because I didn't changed location during installation process of SQL Server. Why would location of my AdventureWorks2012 database be in Download folder only because I attached it from that location? I supposed it will be saved in SQL Server DATA default directory.
May 25, 2015 at 10:20 am
Ah, I understand your thought now.
Attaching the database files does not move them to the default data directory. They will remain in the location from which they were attached.
Cheers!
May 25, 2015 at 1:07 pm
Jacob Wilkins (5/25/2015)
Ah, I understand your thought now.Attaching the database files does not move them to the default data directory. They will remain in the location from which they were attached.
Cheers!
OMG 🙂 I cant believe it. Thank you very much for answer. I found many answers on net which says about moving mdf to default folder but I didnt realize until now why.
Why do you think is that so, attach location is future database location? Its not logical for me at all. Is it just for attach command or restore bak file as well?
I am new to SQL Server but work with others DB for long time.
May 25, 2015 at 1:07 pm
Jacob Wilkins (5/25/2015)
Ah, I understand your thought now.Attaching the database files does not move them to the default data directory. They will remain in the location from which they were attached.
Cheers!
OMG 🙂 I cant believe it. Thank you very much for answer. I found many answers on net which says about moving mdf to default folder but I didnt realize until now why.
Why do you think is that so, attach location is future database location? Its not logical for me at all. Is it just for attach command or restore bak file as well?
I am new to SQL Server but work with others DB for long time.
May 25, 2015 at 1:37 pm
With restoring from a backup it's different, and depends on whether you specify a MOVE clause.
If you don't specify a MOVE clause, then the restore will attempt to create the files at the paths recorded in the backup (and that path may not exist if it's a backup from another server).
If you use a MOVE clause, then the restore will attempt to create the files at the paths specified in the MOVE clause.
Back to the subject of attaching, when you attach the database you are creating a new online database with the file locations supplied (as reflected by the syntax, since CREATE DATABASE...FOR ATTACH is the preferred syntax, because sp_attach_db is deprecated).
Now, if you attach a database from a file location you don't want, you can move the files by using ALTER DATABASE...MODIFY FILE to change the location in the system catalog and then moving the files to the desired location, but you have to take the database offline to move the files.
Hopefully this helps!
June 1, 2015 at 8:16 am
What account was the SQL Server Engine running under?
Surely this account would need full access to the folder your data files were located?
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply