SQL Server – How to Limit Access in a Database System

sql server

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.

Best Answer

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
Related Question