Understanding performance hit of an application role in SQL with ASN.NET

I've rolled my own "approle" in the past, it's not too hard. Create a database role for each type of user (manager, casher, clerk, whatever). Create a database user with the group name (manager_user, casher_user, clerk_user etc). Create accounts for your real users and put them in the database roles. Validate your asp.net users by logging them into the database (open & close a connection), a lookup table or best if you use windows authentication and just get their user name from IIS. Check their membership in a database role but log in to the database using role_user. You can secure the database objects via the role_user, the users don't login and don't have access to any sql objects and you get connection pooling.

I have not used app roles before, but from what I know about the perf hit is that after setting the application role there is no way to revert to the prev. security context. Thus the connection cannot be reused in pooling. This alone is a huge perf. hit that forces you to think twice about using app roles.

However, the docs say that starting from SQL Server 2005 there is a way to remember the original security context in a kind of cookie returned from sp_setapprole and after that use sp_unsetapprole to revert back to it. So the pooling should work again. If I were you, I would compare the perf. with a couple of simple statements/sprocs.

Any reason you don't use the standard ASP.NET membership API on the application level instead of app roles?