Can I force a user to use WITH NOLOCK?
The real answer is:
Stop using NOLOCK
OTHER INFORMATION THAT MIGHT BE USEFUL:
Sure, deny direct access to the tables/views, and enforce data access through stored procedures (or views - thanks Martin) - which you can code to use NOLOCK
.
If you need it to be per user, and you can enforce data access through procedures, you can use the idea suggested here by SQLMenace:
CREATE PROCEDURE dbo.blah
AS
BEGIN
SET NOCOUNT ON;
IF SUSER_SNAME() = N'something'
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
END
SELECT columns FROM dbo.Customer;
END
GO
Or you could also do this:
CREATE PROCEDURE dbo.blah
AS
BEGIN
SET NOCOUNT ON;
IF SUSER_SNAME() = N'something'
BEGIN
SELECT columns FROM dbo.Customer WITH (NOLOCK);
RETURN;
END
SELECT columns FROM dbo.Customer;
END
GO
If that is not possible, I thought of suggesting a plan guide, but I haven't used them to enforce NOLOCK
. Also this document in Books Online suggests that this is reserved for INDEX
, FORCESCAN
, and FORCESEEK
hints. Using a plan guide to try and add NOLOCK
if it isn't already present in the query text will lead to an error. Though, the code shown in Example K seems to contradict that, so maybe it is worth a shot after all. Of course, you'll need to set up a plan guide for every query, which may be prohibitive (or impossible), and they're not exactly for the weak of heart.
And I know you don't want to hear the cons of NOLOCK
, but it would be much easier (and better all around!) to force everyone to use snapshot.
To address Kenneth's suggestion of using a logon trigger: in my testing this SET option can be specified in a logon trigger, but like setting it in a stored procedure, the setting reverts to the server default when the trigger goes out of scope. So no, there doesn't seem to be a way to set a session's isolation level from a logon trigger.
As others have said: don't use NOLOCK willy-nilly. There are certain times where it's appropriate, and 'every query from a specific user' probably isn't one of them. Use one of the better, safer options to deal with concurrency, or your favorite LART if you've got a DBA/developer routinely wedging up the production system with awful queries.
Yes, you could adjust security so they can only select from views that select from base tables with NOLOCK hints, and I could imagine a few situations where that might be called for (operational support staff needing to occasionally examine production data in real-time, perhaps with some sensitive information/columns omitted as well).
But consider this similar discussion:
"How can I get my car up to the third floor?"
"Why would you want to do that?"
If the answer is "Because my business is on the third floor, and I need to display the car for a promo/giveaway", then you start looking at ways to get it up there.
But if the answer is "Because I would like to go home to my third floor apartment," then the proper recommendation is "So park your car in the lot, get out, and take the elevator up to the third floor."
Don't turn assumptions and biases into technical requirements, and don't latch on to your first attempt at a solution. Step back and reevaluate what you want to accomplish.
Seems the nearest you can get without using SPs is using SET READ_COMMITTED_SNAPSHOT
, which will affect all users of the database.
The READ_COMMITTED_SNAPSHOT
database option controls if the isolation level READ_COMMITTED
is implemented like their counterparts in PostgreSQL and FirebirdSQL (statement level snapshot of records) or using locking.
Read more about that in the documentation:
Locking and Row Versioning
You commented in response to another answer:
There is no control application in the scenario in mind. It's direct SSMS against SQL2008R2 Standard Edition
You have to make sure that all that installs are set to Read Uncommitted in Tools/Options/Query Execution/SQL Server/Advanced. I'm not an network SysAdmin myself, so I don't know if a Policy can solve it.