How to force nolock hint for sql server logins

Posted on

How to force nolock hint for sql server logins – This article will take you through the common SQL errors that you might encounter while working with sql, sql-server,  security. The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue. SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem.

SQL Problem :

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.

Solution :

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.

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.

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.

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.

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.

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.

Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code.

Leave a Reply

Your email address will not be published. Required fields are marked *