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

Question

I use MS SQL Server 2005 application roles in an application. I execute the sp_setapprole to start the SPs role and to finish sp_unsetapprole.

"connection pooling doesn't work" with application pooling, and there is no way to react on connection "disconnect event" (execute sp_unsetapprole just before disconnection).

I decide to call sp_setapprole at the start of all my SPs and call sp_unsetapprole at the end of all SPs.

Have you used SQL application roles? What are your XPs? What about performance hits?

Answer 1

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.