In my experience this happens because the application has gaps in functionality or the process would be much faster to complete in SQL then it would in the app. This makes turning off access difficult as these processes are often business critical.
Problems caused: - Short-term - people make mistakes and corrupt/destroy data or cause downtime. - Long-term - data can't be trusted.
I'd be interested to hear people's war stories and the methods/tech used to escape.
My thoughts are: 1. Track scripting 2. Catalogue reasons for scripting 3. Address the functionality gap that will remove the most common reason for scripting 4. Remove access to tables users were updating via SQL before functionality gaps were filled 5. Continue 1-4 until done
You could try to move to the mess we have - at least our updates are being done by app developers. These devs typically have a better shot at not corrupting data or breaking things.
Longer term (and I haven't been successful at this), I would try to convince the dev team that these types of use cases (users executing SQL) represent really fruitful feature requirements. In our case, I'd like to make app changes that allow the users to do these types of things via features in the app. But there are also trade-offs for dev time and whether or not these user-driven SQL hacks are actually features or more like "one-off" tasks . . .
I have hated it when a customer has read access to their own DB to run reports on our app. Even then they can cause performance issues and create dependencies that means they break if we change the schema. In other words their IT adds to our support burden in arbitrary and unpredictable ways.
If you have to allow DB access and write access then use the DB views stored procedures and security features to lock it down. Maybe they think they have access to the User table but infact it’s a view and if they update it runs validations. This allows you to place some business logic and protection between the precious data and the customer.