Eric M Russell (7/20/2016)

---

dndaughtery (7/20/2016)

---

Hey guys, had one of my team members drop our main table by accident. Is there a way to prevent a table from being truncated or dropped? I tried adding a foreign key which allows nulls in a test table but it allowed me to truncate the table.

To best answer this question we need some context about where this needs to be done and who we are attempting to block.

First, dropping or truncating requires either DB_OWNER database role, SYSADMIN server role, or explicit permissions granted to user on the object. None of these should normally be granted to a user in the production environment. If this is a production environment then read up on best practices regarding how to enforce "least privilege", which you can find in various articles and posts here in SQLServerCentral.

Are you asking how to prevent certain types of operations from occurring in development where users can have elevated permissions?