Sunday, August 31, 2008 4:41 PM
I have searched the internet and this forum and have not found an answer...
I am trying to install the AdventureWorks database into my single instance of MS SQL Server Express 2005. I am logged into my machine as an administrator and logged into SQL Server 2005 express 'sa'. I attempt to run the following script:
execsp_attach_db @dbname = N'AdventureWorks',
@filename1= N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf',
@filename2= N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_log.ldf'
The error message I get back is:
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf". Operating system error 5: "5(Access is denied.)".
The folder "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" and all the files in it are read-write. I am 100.0000% certain the files "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf" and "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Log.ldf" exist! They are the result of running the installation program AdventureWorksDB.msi, which I downloaded from: http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004.
What do I have to do to install the AdventureWorkds database????
Sunday, August 31, 2008 5:38 PMModeratorAssure that the ntfs/file permissions give the sqlserver service the rights to access the file.
Sunday, August 31, 2008 5:52 PMModerator
To try to help you, please, could you give some more informations ?
- the operating system (XP/Vista), the edition ( Pro/Home...) and the service pack
- usually, the installer installs the both files in C:\Program Files\Microsoft Sql Server\Samples. Is there any change in the location or have you moved the both files ?
- could you check with the files explorer , that the 2 files are read-write and not read only( find one file, right-click on it, properties and in the 1st tabpage, you should see a checkbox read-only ( if checked , unchecke it ) ?
- have you Sql Server Management Studio Express Edition ( at least SP1 ) ?
If no, download it and use it to attach
in the object explorer,
click on your instance to expand it
right click on databases
in the contextual menu, click on attach
in the new form,click on add
you arrive on a second form : find your file , click on it, and OK
it's the simplest way to attach ( the sp_-attach_db is complicated to type )
the error messages are sometimes more clear in SSMSEE than in Sqlcmd
Try also to attach ( thru SSMSEE or Sqlcmd but in using the windows authentification )
NB: i hope that you are not trying to attach AdventureWorks on a remote instance and on a remote computer ( it would explain access denied )
We are waiting for your feedback to try to help you more efficiently
Have a nice day
Monday, September 01, 2008 6:00 AMModeratorCheck the Service account of SQLExpress. You can use Configuration Manager or Services in control pannel. It might be NT AUTHORITY\NetworkService, change that to a proper user who have permission to this location or make it as Local System account and see.
Madhu K Nair
Monday, September 01, 2008 5:29 PM
That was it. Thanks for the tip about the permission for the service. Still don't fully understand this, but at least now I have the AdventureWorks database.
- John Anderson
Sunday, September 13, 2009 2:43 PMHi All.
I had the same problem -- unable to open physical file on Vista 64. So, I tried to give the SQL Express Service user permissions, run as LocalSystem, etc... nothing..
In my case I had to give my user account (which is an admin) permissions to the file.. Not what I expected the solution to be since I'm in the Administrator group and I have full control...
I figured out the appropriate permissions to use by creating a new database in SSMS and looking at the premissions it had. Perhaps this is because I created the database via the UI in management studio which is running under my user, rather than the SQL Server User.. And, perhaps because I'm trying to attach from the SSMS UI as well.