I have a question related to the limiting of the access to a database network for the users that are allowed to log on to it.

The problem is that the databases are huge (they contain hundred of millions of records) and without a few precautions, letting the logged-in users do whatever they want creates chaos.

For instance, allowing a simple SELECT query to run without any constraints might unnecessarily clog the access to the databases for other users.

So far, the following recommandations have been made in order to limit the access:

  1. Using the "SET QUERY_GOVERNOR_COST_LIMIT 900" clause in the query windows in order to limit the usage of the database up to a quarter of hour.

  2. Limiting any SELECT queries using "TOP 15" clauses in order to limit the retrieval of records from those hundred milion records to a bare sample of fifteen records.

  3. Using the "WITH (NOLOCK)" and "WITH (READUNCOMMITTED)" clauses in order to prevent the databases from being locked. Of course, there are other differences between these two clauses that have been already discussed elsewhere.

  4. Using scheduled tasks that programatically kill any unatended connections at a specific moment of time when supposedly the users should have already closed all their work, so that the relational database management system does not have any unnecessary load left over the termination of the user interaction with it.

Are there any other suggestions that might affect the speed of the database usage?

Of course, most of the allowed usage is strictly limited to querying. Manual updating and inserting of records is very infrequent. The creation of temporary tables is not recommended and it is almost prohibited.

Any other type of usage can be remotely isolated by using other relational database management systems, where - through link servers - data can be brought in and manipulated without further restrictions.

  • Are you able to service most of the user's query needs with a few stored procedures perhaps? I'd be cautious giving them full SELECT rights even with these precautions in place. – LowlyDBA Apr 10 '15 at 12:59
  • How are you going to enforce TOP 15? And if you allow them to dictate ORDER BY, this isn't necessarily going to help. TOP 15 FROM <massive table> ORDER BY NEWID() is going to spend most of its work sorting, so the number of rows you actually return is a very small piece of the picture. – Aaron Bertrand Apr 10 '15 at 13:38
  • Also, be sure you are aware of all of the risks with using NOLOCK. – Aaron Bertrand Apr 10 '15 at 13:39

Allowing even trusted users to login to what appears to be a production database is in my mind a big no-no. The solutions you mention above may provide you with a sense of security/relief but they their own have side effects.

I would be particularly worried about your statement

Manual updating and inserting of records is very infrequent

If you have users who are updating/insert records then I am sure they are also deleting records most likely with no or a very limited audit trail.

If this was my database I would

  1. Immediately prevent all users from accessing the database tables and running any SQL statements such as Select, Insert, Delete, Update etc
  2. Sit down with the end users who are requesting data and determine exactly what data is required. Based on the end user requirements create a set of SPs that produce the required data.
  3. A DB account should be created that would only have execute permissions on the SPs
  4. If possible determine if the reports really have to be run in real time on a production database. If not I would suggest a snapshot be created on a regular basis on which the reports are run.
  5. I would also ensure that the sa account has a strong password and is not used on a day to day basis and that the password is know to only 1-2 people.
  6. Monitor the database over a period of time to ensure users have not gained additional privileges
  • As I see the thing going, it will eventually lead to only one person who maintains some stored procedures. Even if any other quicker access is needed, it can be further frozen down into a stored procedure and afterwards into a report. Good answer. – user62547 Apr 14 '15 at 7:05

Use read-only copies of subsets of the data to service users. Combine with views/stored procedures and you have solid control over who can how much data and through what means.

Refresh the data periodically to suite the needs of the users.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy