如何对 SQL Server 登录强制使用 nolock 提示
有谁知道如何对某个用户发出的所有交易强制执行 nolock 提示？ 我想为支持团队提供一个登录名来查询生产系统，但我想通过对他们所做的一切强制执行 nolock 来保护它。 我正在使用 SQL Server 2005。
Does anyone know of a way to force a nolock hint on all transactions issued by a certain user? I'd like to provide a login for a support team to query the production system, but I want to protect it by forcing a nolock on everything they do. I'm using SQL Server 2005.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
好的，您需要澄清您要在这里做什么。
如果您试图减少数据库上的锁定，并可能为支持用户提供可能永远不会真正提交到数据库中的数据。 在允许他们向数据库写入任何内容的同时，nolock 是最佳选择。 您将获得额外的好处，即您的用户仍然能够使用 SET TRANSACTION ISOLATION LEVEL 命令提高其隔离级别。
如果您试图限制它们在针对数据库运行内容时可能造成的损害，请考虑实施安全性，确保只允许它们对表进行读访问，并考虑剥离对存储过程和函数的所有访问权限。
我发现 NOLOCK 在堆栈溢出方面被严重误解。
OK, you need to clarify what you are trying to do here.
If you are trying to reduce locking on the database and possibly provide your support users with data that may never really get committed in the database. While allowing them to write anything they want to the database, then nolock is the way to go. You will get the added bonus that your user will still be able to increase their isolation level using the SET TRANSACTION ISOLATION LEVEL command.
If you are trying to restrict the damage they can cause when running stuff against the DB, look at implementing security, make sure they are only allowed read access to your tables and look at stripping all access to stored procs and functions.
I Find NOLOCK is heavily misunderstood on stack overflow.
这是一种痛苦且笨拙的方法，但这就是我工作的地方正在做的事情。 我们还使用经典的 asp，因此我们使用内联 sql 调用。 我们实际上将 sql 调用包装在一个函数中（在这里您可以检查特定用户），并将“SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED”添加到调用的开头。
我相信在功能上这与无锁提示相同。 抱歉，我没有纯 SQL 答案，我很想听听您是否找到了一个好的方法来做到这一点。
This is a painful and hacky way to do it, but it's what we're doing where I work. We're also using classic asp so we're using inline sql calls. we actually wrap the sql call in a function (here you can check for a specific user) and add "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" to the beginning of the call.
I believe functionally this is the same as the no lock hint. Sorry I don't have a pure SQL answer, I'd be interested to hear if you find a good way to do this.
您可以配置支持人员的 SQL Management Studio，将默认事务隔离级别设置为 READ UNCOMMITTED（工具 -> 选项 -> 查询执行 -> SQL Server -> 高级）。 这在功能上与对所有内容都提供 NOLOCK 提示相同。
缺点是您必须为支持团队的每个成员执行此操作，并且他们能够更改 SQL Management Studio 上的配置。
You could configure your support staff's SQL Management Studio to set the default transaction isolation level to READ UNCOMMITTED (Tools->Options->Query Execution->SQL Server->Advanced). This is functionally the same as having NOLOCK hints on everything.
The downsides are that you'd have to do this for each member of your support team, and they'd have the ability to change the configuration on their SQL Management Studio.
正如埃斯波暗示的那样，我很确定没有直接的方法可以满足您的要求。 正如他所说，您可以通过限制用户只能访问具有内置 NOLOCK 编码的进程来实现此目的。
As Espo hinted at, I'm pretty sure there's no direct way to do what you're asking. As he said, you can sort-of accomplish it by limiting the user's access to only procs that have built-in NOLOCK coded in them.
您可以为支持团队创建一个受限用户，然后使用 nolock-hint 编写存储过程或视图。 然后只授予对这些的访问权限，而不是直接的表选择访问权限。
You could create a limited user for the support team, and then either write stored procedures or views with the nolock-hint. Then only give access to those and not direct table select access.
不幸的是，将用户限制为 SP 违背了其目的。 我希望有某种方法可以让他们查询所有内容，从而提高他们的故障排除技能。 谢谢你们的帮助。
Unfortunately, restricting the users to SPs defeats the purpose of this. I was hoping there was some way I could allow them to query everything and thereby enhance their troubleshooting skills. Thanks for your help guys.