Prevent SQL Server Table operations (INSERT and DELETE) on some tables

Question

While working with some random sql queries on our databases, we may not want to insert or delete items to some of the database tables by just typing their names by mistake. So how to make them locked to the "editing", to be able to work safe.

Thanks.

Answer 1

Why not just define a role in your database and give that role rights to whatever tables your users need, but this role would not have update/delete rights to the tables you are concerned with?

Answer 2

Work with a limited-rights account, and deny it rights to modify the 'protected' tables.

Answer 3

As the others have mentioned, this should be set up in the Roles.

Here is a useful link on Understanding Roles in SQL Server 2000

Answer 4

Roles are the best practice way to go. However if you can't/won't use roles, you could use triggers, see this answer: http://stackoverflow.com/questions/2551510/sql-server-query-editors-any-that-warn-of-number-of-rows-to-be-changed/2552658#2552658

Answer 5

Move these special tables to its own database. Give the user account only select privileges for this database.