在 SQL Server 2005 中,是否可以基于每个用户设置事务隔离级别? [英] In SQL Server 2005, is it possible to set transaction isolation level on a per-user basis?
问题描述
在 SQL Server 2005 中,是否可以自动将事务隔离级别设置为,例如,基于每个用户读取未提交的内容?
In SQL Server 2005, is it possible to automatically set transaction isolation level to, say, read uncommitted an a per-user basis?
(例如,如果我为用户 Fred 设置了这样的登录选项,Fred 就不必记得在他的 select 语句中使用 NOLOCK 提示,或者记得将设置的事务隔离级别语句添加到他的 sql 中.)
(So for instance, if I set such a login option for user Fred, Fred wouldn't have to remember sprinkle his select statements with NOLOCK hints or remember to add in a set transaction isolation level statement to his sql.)
推荐答案
如果是存储过程,你可以在 procs 中有这样的代码
if it is for stored procs you could have code like this in the procs
if user_name() in('dbo','username','otherusers','bla')
set transaction isolation level read uncommitted
else
set transaction isolation level read committed
当然现在你有一个维护问题,必须修改所有当前的procs
But of course now you have a maintenance problem, all current procs have to be modified
你也可以用 suser_sname() 来检查,但这真的变成了一个很大的 PITA
you can also check with suser_sname() but this really becomes a big PITA
您也可以使用 登录触发器没有尝试过,不确定它会起作用
you might also be able to use a LOGON Trigger I have not tried and am not sure it will work
这篇关于在 SQL Server 2005 中,是否可以基于每个用户设置事务隔离级别?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!