We hit another “gotcha” with connection pooling with a web application a long time back.

The ASPs called a DLL that uses SQL authentication (I know, I know) to access the database. We implement all database access through stored procedures, but there was this one routine where the developer decided to build and submit a SELECT statement within a stored procedure. Rather than harass him to do it differently I set up an application role with access rights to support the query, and the application acitvated the role if and as necessary.

This, however, messed up other ASP pages, as the application role didn’t have execute stored procedure rights (as I am always harsh over database access privileges), but it took a while to figure out. Say we had 20 connections open in the pool to support the web site; at any point in time, 0 to 20 of them could be working under the context of this application role, and this status is “undetectable” by the pool manager. It’d assign a connection based on server, login, database (and/or whatever else), but was completely unaware of application role status, resulting in a connection being assigned with inappropriate rights.

In the end I dumped the application role and harassed the developer to change his code. If there were a way to “undo” or roll back the application role setting, this would not have been a problem.

Philip