SQL to end users: pros and cons [closed]

Question

Once upon a time, far far away in a distant galaxy I was a witness to a discussion between a couple of developers. The subject was if it's a good idea to give end users access to a production SQL server (MS SQL 2008) in order to give them ability to make their own reports against their own data.

It was a request from business people -- "Our clients want to make custom reports".

A guy who proposed this, claimed that:

1. He was able to give users "read-only access" through set of permissions and make the system absolutely safe.
2. SQL initially was an "end-user" language and may be so now.
3. Having permissions to run SQL queries, users would be able to do what they want not disturbing developers and support.

Other guys who were against this claimed:

1. It's pretty easy to crash MS SQL having even max. restricted access.
2. Exposing SQL and database structure to end users is not a good idea anyway; it's a bad design.
3. SQL is too complicated to non-programmers, and, therefore, it won't make their life easier.

What do you think about giving end users access to SQL?

Thank you in advance!

Answer 1

You can make some things safer by not just limiting users to read-only access, but also by turning on the query governor cost limit. That will attempt to do some cost analysis of the queries prior to running them, and if they exceed the predefined threshold it will refuse to run them.

Even better than this would be to have a cloned database available for querying. This could be something as simple as a separate server running off of a backup of the production system. Depending on how "live" your data needed to be, you could adjust the backup/restore interval accordingly.

As far as whether it's a good idea to expose the database to direct querying for non-programmers, that still depends on just how savvy the users are. Could they be taught SQL? It's really not that hard for simple things.

Answer 2

This depends entirely upon your user(s). If the user is technically capable or interested in developing this skill set, they can alleviate a big chunk of the workload from the programmers.

One the other hand, I know boneheads that will never understand normalized data structures, and their runaway queries have repeatedly crashed the server.

If you decide to go for it, create a separate database mirror for reporting if possible. Update it nightly so they have fairly current data. Of course, this isn't ideal if they have real-time reporting needs.