One of the poorly documented, yet quite important, facts of life in dealing with .NET applications is that one is often using Windows Authentication to communicate with Sql Server databases. Unfortunately, exactly which identity one is using by default is poorly documented at best. So I made you this handy table for reference:
|Windows Version||Web Server||Default Windows Context|
|Any||Cassini (VS.NET 2005 builtin web server)||Interactive User’s Context*|
|2000 (Pro or Server)||IIS||ASPNET|
|2003 Server||IIS||NT AUTHORITYNETWORK SERVICE**|
*: This means you, the developer’s context. Which usually translates to a member of the Administrator’s group, meaning your application is running with full database permissions. Meaning it is a bad testing situation because the web app should never, ever have those sorts of privileges.
**: This is the default identity for the default application pool in IIS6. In any case, the web application takes on the context of the hosting application pool.
As for successfully transferring security from development to production, the biggest hint I can give is to create Database Roles in your database, and assign permissions to those roles. The main issue here is that transferring Sql Server users can be difficult, whereas a database role is wholly contained within the database and is much more easily transferable. And you can always create a new user with appropriate roles when you move to production. An act much easier than manually assigning appropriate permissions to the new user. And much safer than just giving the production user blanket db_owner permissions to the web user.
Enjoy and tame them Sql Dbs.