SQL 2005 attach db error

[mkal]

I have a new 64bit sql 2005 server w/sp3 on Windows 2003 /sp2

I am trying to attach a sql 2000 database using the following sql statements i.e. without attaching the log file.

CREATE DATABASE Test
ON (FILENAME = 'D:\MSSQL\Data\Test_Data.MDF')
FOR ATTACH_REBUILD_LOG

I receive the following error: Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "D:\MSSQL\Data\Test_Data.MDF". Operating system error 5: "5(Access is denied.)".

The account running the command is a domain user w/sysadmin rights. Also, tried running this under the 'sa' account with the same outcome.

Also tried running this stored procedure:

exec sp_attach_single_file_db @dbname= 'Test', @physname= 'D:\MSSQL\Data\Test_Data.MDF'

Received the same error message.

I know the files were cleanly detached and moved w/o a problem because I can attache the database if the log file exists. I need to test this other method as well and get it to work.

 

[JayKusch]

Are you logged on the physical machine that you are attempting to attach the DB file?

IF not, log on to the server and try the commands.

Looks to me like an NT security issue and not a SQL security issue persay

Thanks

J. Kusch

 

[mrdenny]

When SQL Server 2005 detaches a database it changes the rights on the file to the user who told SQL to detach the file. You have to reset the rights on the file so that the SQL Server's account has rights to access the file again.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[mkal]

Thanks guys it was an NT issue once I resolved that I could attach the db!

Mike