Access Denied error when trying to attach AdventureWorks2012 .mdf file
-
I've installed SQL Server 2012 (Enterprise trial) onto my laptop running Windows 7 Home Premium.
I've downloaded the AdventureWork2012 database to my C:\Downloads folder.
When I run this statement I get error,.
CREATE DATABASE AdventureWorks2012
ON (FILENAME = 'C:\Users\MyName\Downloads\AdventureWorks2012_Data.mdf')
FOR ATTACH_REBUILD_LOG ;
--error
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "C:\Users\MyName\Downloads\AdventureWorks2012_Data.mdf" failed with the operating system error 5(Access is denied.).
I make sure to run SQL Server Studio as administrator. I am at a loss as to what to do. Any suggestions or questions that could help me get to root of problem?
--Quote me
-
Just to be sure. How do you make sure that you are running SSMS as an administrator?
-
polkadot (9/13/2012)
I've installed SQL Server 2012 (Enterprise trial) onto my laptop running Windows 7 Home Premium.I've downloaded the AdventureWork2012 database to my C:\Downloads folder.
When I run this statement I get error,.
CREATE DATABASE AdventureWorks2012
ON (FILENAME = 'C:\Users\MyName\Downloads\AdventureWorks2012_Data.mdf')
FOR ATTACH_REBUILD_LOG ;
--error
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "C:\Users\Helen\Downloads\AdventureWorks2012_Data.mdf" failed with the operating system error 5(Access is denied.).
I make sure to run SQL Server Studio as administrator. I am at a loss as to what to do. Any suggestions or questions that could help me get to root of problem?
its not SSMS that needs to be run as administrator. it is the Sql Server Engine that does not have access to the file. when installing Sql Server what user did you select for Sql Server to run as?
For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url] -
Hellooo Capn Hector to the rescue. Thank you very much.
To answer the first question, I run SSMS as admin by right clicking on the Sql Server Management Studio from start program->program list.
To answer CH's question, I've taken a screen shot of all the logons associated with the different services and attached it. I accepted all default logon settings during installation which for SQL Server is NT Service\MSSQLSERVER.
*If* this does not answer the question, *then* how to I find the user that I selected for SQL Server Engine?
--Quote me
-
I don't use SQL Server Express but you should have the COnfiguration Manager where you can check the Account that is used for the SQL Server, SQL Server Agent, etc.
You can also check under Services but it is not recommend that you change the Logon from Services.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Welsh Corgi, I'm not sure why you are alluding to SQL Server Express when I am using 2012. Also, the attached screenshot is of Configuration Manager...
--Quote me
-
polkadot (9/13/2012)
Welsh Corgi, I'm not sure why you are alluding to SQL Server Express when I am using 2012. Also, the attached screenshot is of Configuration Manager...ok, your running the trail version of SQL Server 2012 Enterprise Edition.
I do not open attachments and a lot of people are reluctant to do so..
As was previosly stated it does not matter what SSMS runs as.
You were asked what Account are you using for the SQL Server Agent Service? Do you need someone to look at your screen shots to tell you what account you are using? :w00t:
I would bet that the account that you are using does not have permissions to the folder where your mdf is located.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
You were asked what Account are you using for the SQL Server Agent Service? Do you need someone to look at your screen shots to tell you what account you are using? :w00t:
But, in addition to attachment I also stated that SQL Server is running as NT Service\MSSQLSERVER. If this doesn't answer question let me know. And, SQL Server Agent is running under NT Service\MSSQLSERVERAGENT
I would bet that the account that you are using does not have permissions to the folder where your mdf is located.
Can we first make sure that I've identified the 'account' properly, so that I can proceed to determining if it has permissions to the folder. I have also gone to the Downloads folder and maxed out (checked Full control) to every user listed in Properties->Security tab.
Since the problem is a show stopper I am all ears.
--Quote me
-
What happens if you set the SQL Server Agent Service to run as a Domain Account?
As a test change the SQL Server Service to run using your WIndows Domain Account.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Since you stated that SQL Server is running as NT Service\MSSQLSERVER I'm curious as to why are you asking for people to look at your attachment?
You already answered the question that you needed to address.
Change the Account to a Domain Account that has permissions to that folder or set the Account to the Local Administrator permissions to that folder and you are good to go.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
OH YOUR BLESSED GOODNESS. My domain account I believe is AKA the Windows account/password I used to log on to my laptop hundreds of times a day.
When I changed to domain account, the gravity of which I am only beginning to comprehend, it worked.
I finally got the expected outcome, based on Pinal Dave's blog:
File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Log.ldf" may be incorrect.
New log file 'C:\Users\Helen\Downloads\AdventureWorks2012_log.ldf' was created.
Converting database 'AdventureWorks2012' from version 705 to the current version 706.
Database 'AdventureWorks2012' running the upgrade step from version 705 to version 706.
And the database is indeed there and so are the tables. Welsh Corgi, thank you.
--Quote me
-
Now I would like to reattach the old 2008 databases, but there is a problem there... if you don't mind saying a few words about it I'd appreciate. The data folder seems to be locked down.
(1) there a lock symbol on the DATA folder
C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA
(2)
When I run
CREATE DATABASE AdventureWorks2008R2
ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2_Data')
FOR ATTACH_REBUILD_LOG ;
--error
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2_Data". Operating system error 2: "2(The system cannot find the file specified.)".
--Quote me
-
polkadot (9/13/2012)
Now I would like to reattach the old 2008 databases, but there is a problem there... if you don't mind saying a few words about it I'd appreciate. The data folder seems to be locked down.(1) there a lock symbol on the DATA folder
C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA
(2)
When I run
CREATE DATABASE AdventureWorks2008R2
ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2_Data')
FOR ATTACH_REBUILD_LOG ;
--error
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2_Data". Operating system error 2: "2(The system cannot find the file specified.)".
I'm not sure about the locked symbol, I never saw that.
The following statement specifies a folder but it omits the file name and/or the extension of the mdf.
CREATE DATABASE AdventureWorks2008R2
ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2_Data')
FOR ATTACH_REBUILD_LOG ;
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
I added .mdf and it solved the problem. Another database attached. Thank you so much.
--Quote me
-
Your welcome, no big deal. Glad to see that you are good to go.:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply