How to prevent database user deleting data from ALL tables by triggers

Hi Experts How I can prevent database user deleting any data in tables using triggers? I want just A开发者_开发知识库dmin delete Data from tables

Thanks

---

Umm take away that users permission? If you don't want them doing something, 'disallow' them that right... thats why we have permissions.

Here are details on how to revoke permissions:

http://msdn.microsoft.com/en-us/library/ms186308.aspx

---

Any particular reason you want to use triggers?

You could simply remove the DELETE permission from the users you want to restrict. Have a look at the "Permissions" section here: http://msdn.microsoft.com/en-us/library/ms189835.aspx

EDIT: Since you say you do want to use triggers (but I really think you should reconsider) you can create a table such as:

CREATE TABLE Restricted_Users 
(
  user_name VARCHAR(40) PRIMARY_KEY -- Use a size appropriate to your requirements
)

Create INSTEAD OF DELETE triggers on all your tables (that's going to be a chore) which checks for the USER_NAME() in the Restricted_Users table and if they EXIST you can call RAISERROR to cause the transaction to be rolled back and display a message to the user.

Remember you will have to maintain these triggers on all new tables added to the database as well as maintaining the list of users in the Restricted_Users table whenever you add/remove users from the database.

It would be a lot simpler to use the permission system available in SQL Server (it's what it's designed for) using roles with appropriate permissions set for the tables. Then, when adding new users you only have to assign them to the appropriate role and the delete permissions are handled for you.