SQL Server application role, performance hit

问题:

I plan to use MS SQL Server 2005 application roles in my application. I will start the role by executing the sp_setapprole and finish by executing the sp_unsetapprole SPs. The application is implemented in ASP.NET.

I've read that "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 plan to call sp_setapprole at the start of all my SPs and call sp_unsetapprole at the end of all my SPs.

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

回答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.

回答2:

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?