How to force nolock hint for sql server logins

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

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.