Can I force a user to use WITH NOLOCK?

Question

Can I force a user's queries to always run with the hint NOLOCK? e.g. they type

select * from customer

But what is executed on the server is

select * from customer with (nolock)

This question is not:
About the various pros and cons of NOLOCK, respectfully. I know what they are, this is not the place to discuss them.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

Similar to the Database Snapshot option or having a reporting mirror, but how about using AlwaysOn and read-only routing with SQL 2012? You can redirect a connection to the readable secondary via the ApplicationIntent=ReadOnly facet of the connection string.

Have your application determine the connection string depending on the user name then it is completely transparent to the user. The secondary is kept relatively up-to-date depending on latency without the overhead of maintaining a database snapshot.

Scenario 1 Senior user logs into application, application determines user should have read/write permission. Connection string created to primary database.

Scenario 2 User logs into application, application determines user should have read only permission. Connection string created including ApplicatonIntent=ReadOnly. Connection made to readable secondary database. If write-activity is required, a separate connection to primary would be required.

Availability Group Listeners, Client Connectivity, and Application Failover http://msdn.microsoft.com/en-us/library/hh213417.aspx#connecttosecondary

"Read-only workloads use row versioning to remove blocking contention on the secondary databases. All queries that run against the secondary databases are automatically mapped to snapshot isolation transaction level, even when other transaction isolation levels are explicitly set. Also, all locking hints are ignored. This eliminates reader/writer contention."