SQL Server应用程序的作用,性能损失 [英] SQL Server application role, performance hit
问题描述
我计划在我的应用程序中使用MS SQL Server 2005的应用程序角色。我将开始执行的作用 sp_setapprole
和完成通过执行 sp_unsetapprole
的SP。该应用程序在ASP.NET中实现的。
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.
我读过,连接池不起作用与应用程序池,也没有办法联系上断开事件 sp_unsetapprole
之前作出反应(执行断开)。
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).
我打算叫 sp_setapprole
在我所有的SP和电话 sp_unsetapprole
在年底开始我所有的SP的。
I plan to call sp_setapprole
at the start of all my SPs and call sp_unsetapprole
at the end of all my SPs.
你有没有使用SQL应用程序角色?你有什么XPS?有关性能影响是什么?
Have you used SQL application roles? What are your XPs? What about performance hit?
推荐答案
我滚过去我自己的approle,这不是太难。为每种类型的用户数据库角色(经理,出纳,文员,等等)。创建组名(manager_user,casher_user,clerk_user等)的数据库用户。你真正的用户创建帐户,并把它们放到数据库角色。通过登录他们到数据库验证您的asp.net用户(开放和放大器;关闭连接),查找表或最如果您使用Windows身份验证,并从IIS刚刚获得自己的用户名。检查他们的会员数据库中的作用,但使用ROLE_USER登录到数据库中。您可以通过ROLE_USER确保数据库对象,用户不登录,不访问任何SQL对象,你会得到连接池。
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.
这篇关于SQL Server应用程序的作用,性能损失的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!