Attach database from network drive

Hello,

when I attach an database from a network drive in SQL Express 2005 the access permissions on the data and log files will be change.
This ends in a deadlock when the user doesn't have administrator right on the network server, because the permissions will allow access only for administrators and an unknown Usergroup on the network server. I think it's the SQLServer2005MSSQLUser$xxx$MSSQLSERVER group from the local server.

Example:

DBCC TRACEON(1807)
GO
PRINT 'ATTACH FIRST'
CREATE DATABASE TEST ON
( FILENAME = N'\\server\tmp\test.mdf' ),
( FILENAME = N'\\server\tmp\test_log.ldf' )
FOR ATTACH;
GO
PRINT 'Detach'
exec sp_detach_db TEST
GO

When this batch runs again after some minutes you will get

Msg 5120, Level 16, State 101, Line 2

Die physikalische Datei "\\server\tmp\test.mdf" kann nicht ge?ffnet werden. Betriebssystemfehler 5: "5(Zugriff verweigert)".

This could be translated like:

The physical file "\\server\tmp\test.mdf" could not be opend. OS Error 5 (Access Denied).

This error is the same when the user doesn't have full access on the share and db files.

In SQL2000/MSDE no security permissions will be changed, so the database could always be attached.

Is there any workaround?

Perhaps setting the security permissions could switched off?

You also need full access permissions on the share and files, wich also doesn't make sense.

Hi Dieter (thanks for translating ;-)),

you have to make sure that the SERVICE account which is starting up the SQL Server service has the appropiate permissions on the network drive. Unless this can′be accomplished you can′t attach the database via network.

HTH, Jens Suessmeyer.

|||

Hi Jens,

The Service account has permissions on the network drive.

The problem is that SQL Express need full rights because of changing the permissions when attaching the db files.

First time the database could attached, but then the permissions are changed an the database could not attach again.

Dieter Pelz