deploy asp.net application with sql server express database

[Loane Sharp]

Hi there

I had no luck in getting the following question answered in the SQL Server
newsgroups. Please point me in the right direction.

Background: I am using the "System.Data.SqlClient" namespace and a
SqlConnection() object to connect to a local SQL Server 2005 Express
database from within VB code contained in <SCRIPT /> tags in an ASP.NET
page. I use Windows authentication (ie. "Integrated Security=SSPI;") to
connect to the database, and Windows then uses the (local)\ASPNET account to
make a connection.

Problem: Every week there is a new database to attach. Making use of the
XCopy functionality with AutoClose enabled, I simply delete the old .mdf
file, copy the new .mdf file to that directory, and then let the
"AttachDBFilename=..." part of the connection string attach the database.
The problem is, the database file I want to attach is created on a different
SQL Server, and as such does not contain login and permissions for the local
machine's ASPNET user account. At present I am manually adding the login
(exec sp_grantlogin '(local)\ASPNET') and user (exec sp_grantdbaccess
'(local)\ASPNET') each time I attach a new database. Surely there must be an
automatic way?! I can but I'd prefer not to give the ASPNET account login
and database access from the SQL Server that I use to create the databases,
since that would mean keeping track of the various user, machine and network
names, which would be a nightmare.

Please help!

Best regards
Loane

 

[Jéjé]

if your aspnet account is a "system administrator" then this account should
access all databases.

 

[Loane Sharp]

isn't that a bit dangerous?

 

[Jéjé]

yeap...
the only other solution is to restore the access to the user like you do it

 

[Chris Botha]

How about impersonation? If you are not familiar with it, the declaration
comes in the web.config file inside the <system.web> section and to
impersonate the connecting user, use
<identity impersonate="true"/>
or to impersonate a specific user, use
<identity impersonate="true" userName="domain\UserName"
password="whatever"/>

 

[Loane Sharp]

thanks very much Chris, Jeje, all worked perfectly. Best regards Loane