An Ounce of Prevention

Recovering quickly from isolated corruption is an admirable goal and an impressive achievement. If you carefully develop plans for dealing effectively with data loss and damage, you will undoubtedly reduce the toll that corruption takes on your business. More than anything, though, you should direct your initial design, implementation, and testing efforts to preventing or minimizing human error. For example, if you let users manipulate data only through stored procedures, views, and functions instead of allowing direct base-table access, you can minimize accidental deletes and updates that result from incorrect or nonexistent WHERE clauses. Just make sure that you test the stored procedures thoroughly before you make them available to users.

By design, you can prevent a user from dropping a table. First, only the table owner, a member of the db_ddladmin or the db_owner role, or a systems administrator (sa) have the appropriate rights to drop a table. Second, you can prevent a drop from being successful even if someone who has the permission to drop the table attempts a drop. If the table is being referenced by a foreign key or otherwise solid dependency chain, SQL Server won't allow the referenced object to be dropped until the reference (i.e., the dependency) is removed. (The dependencies force the dependent objects to stay intact with no modifications or removal.) However, not every object has a foreign key dependency on it. When an object has no foreign key dependency on it, you can use schemabound views to create a dependency chain for the sole purpose of preventing the accidental table drop. For a description of how to use schemabinding to prevent accidental table drops, see "T-SQL Tutor Quick Tip," InstantDoc ID 22073.