0

I want to deny query for insert,update,delete,alter in database engine only? is there a way to do that ? Because I have an application that uses the SQL database server and has a lot of users that consists of a lot of levels. I use the connection to the database with ADODB. How do I only can execute insert, update, delete or procedure only on the application, but can not run in SQL server database engine?

example: users A can insert,delete or update data into 'customer' through application, but cannot execute query that contain insert,delete, or update from database engine. because is too risky if users do that and can manipulate data from there.

Thanks Before

7
  • You want to prevent users executing direct queries and allow access only through application? Is the application running on users' workstation or on a (web) server?
    – vonPryz
    Commented Aug 1, 2016 at 5:37
  • @vonPryz the applicaton running on users' workstation
    – Ferdianto
    Commented Aug 1, 2016 at 6:05
  • What's the problem you are trying to solve by limiting access? Is this about security? Data integrity? Something else?
    – vonPryz
    Commented Aug 1, 2016 at 6:09
  • it's about security, so users cannot executing direct queries and must from application. do you have a idea @vonPryz
    – Ferdianto
    Commented Aug 1, 2016 at 6:53
  • It would be much easier to help you if you'd edit the post and add more details. Please explain what's going on and what kind of behavior you want to prevent and why.
    – vonPryz
    Commented Aug 1, 2016 at 6:56

1 Answer 1

0

Since the application is running on user workstation, it is likely to be run on user's own account. This, unfortunately, means you are out of luck with the simple solution - setting up user permissions.

What could work is an approach based on stored procedures with EXECUTE AS.

There are lots of tutorials in the net. For a summary:

  1. Create a user account for application usage and grant it appropriate rights to the database objects.
  2. Create stored procedures to handle all the required data operations. Use EXECUTE AS to run those procedures as the application user.
  3. Grant your users rights to execute the procedures.
  4. Revoke other permissions from the users.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.