On SQL Server, is it possible to restrict certain users from using certain functions, operators or statements?

Question

we support a large number of developers and analysts who either do not know much or don't care at all about query performance. We have every day hundreds of poorly conceived queries (also due to poorly designed db architecture) that, each, suck away dozens of gigabytes of RAM from our servers and have execution plans in the range of megabytes stored in SQL cache.

We would like to restrict certain users from using SORTs and HASH MATCHes, specifically ORDER BY, SELECT DISTINCT and JOINs of any kind. Our intention is to let users pull their data, but work on it only on their side.

Could this be achieved in SQL Server? Is there any DB engine that supports that? What would be your thoughts about this?

I know this would beat the principle of a relational database, but we're looking for some way to enforce that the engine is properly used and not abused by uneducated users.

We are already considering the implementation of Resource Governor to allocate a certain amount of machine resources for these bad queries.

Answer 1

The answer by kevinsky is one path towards a solution for you.

A different path for your specific issue is to create a second reporting (and maybe a third dev) server(s). With copies of the production data. Your reporting people and development people can do all kinds of stupid, and not impact production.

There are multiple ways of achieving this, depending on how fresh your report/dev data needs to be.

A common solution is a nightly backup and restore to the secondary server. If you need really current data (like no lag or one second) look into SQL Server Always On

Answer 2

You can create views of the data they need and create a role that only allows select.

Or you can create stored procedures that return a result set that can be any combination of tables accessed with a query that performs well.

I do not know of any way to restrict the use of basic commands in SQL short of some hacky database level trigger and suggest that if you have that many users who don't know how to write a good query then some education is a more effective way to do this. Lunch time sessions?