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:

Notes:
*: 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.

This entry was posted on Wednesday, November 1st, 2006 at 11:45 am, contains 279 words, and is filed under .NET. You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed. The views and opinions in this blog post are those of its author.