Can I force a user to use WITH NOLOCK?

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.

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.

StackExchange Database Administrators Q#47357, answer score: 18