When following method of attaching sqlexpress mdf file from http://msdn.microsoft.com/en-us/library/ms165673.aspx , I meet an error in win2k3. The procedure is:
1. Create a ASP web application in default location
2. Create a database inside app_data directory
3. Close the VS solution
4. In command line: sqlcmd -E -S .\sqlexpress
5. Do the following , and will see error:
1> USE [master]
2> GO
Changed database context to 'master'.
1> CREATE DATABASE [database1] ON
2> ( FILENAME = N'C:\Documents and Settings\username\My Documents\Visual Studio 2008\Projects\WebApplication1\WebApplication1\App_Data\database1.mdf' ),
3> ( FILENAME = N'C:\Documents and Settings\username\My Documents\Visual Studio 2008\Projects\WebApplication1\WebApplication1\App_Data\database1_log.ldf' )
4> FOR ATTACH ;
5> GO
Msg 5133, Level 16, State 1, Server XINYANGQIU04-V1\SQLEXPRESS, Line 1
Directory lookup for the file "C:\Documents and Settings\username\My Documents\Visual Studio 2008\Projects\WebApplication1\WebApplication1\App_Data\database1.mdf
" failed with the operating system error 5(Access is denied.).
1>
Note, if creating the WAP in a non-default directory, such as c:\others, we are fine.
1> CREATE DATABASE [database_name] ON
2> ( FILENAME = N'C:\others\WebApplication1\WebApplication1\App_Data\database1.mdf' ),
3> ( FILENAME = N'C:\ others\WebApplication1\WebApplication1\App_Data\database1_log.ldf' )
-->No error. And database is attached.
To workaround this, add "[localComputerName]\Users" with read/Execute security permission to the default project location app_data folder
For information, the default security setting is as following
Administrators
username (current login user, admin)
SYSTEM
Each with full control
In non-default location C:\others\WebApplication1\WebApplication1\App_Data, the security setting is:
Administrastors (full control)
Creator Owner (special permissions)
SYSTEM (full control)
Users (Read&Execute,List,Read, special permission)