T-SQL Tutor Quick Tip

I often encounter the complaint that accessing production databases is too easy, especially from Enterprise Manager. To make a system both secure and highly available, you need to have administrative change and maintenance processes in place to minimize direct access to production databases. However, I have a quick tip that can help in the interim.

Recently someone asked me, "How can I prevent a systems administrator (sa) from dropping a production table?" I realized that using declarative referential integrity (DRI) makes inadvertently dropping a table more difficult. However, DRI prevents a drop table only when the table is being referenced with a foreign key constraint. A sales table, for example, often references other tables, but other tables don't always reference it. So how can you prevent an accidental table drop? In SQL Server 2000, consider using a schema-bound view.

T-SQL Tutor quick tip of the month: Using WITH SCHEMABINDING, create a view on any tables you want to protect. This tip prevents an administrator from dropping the tables on which the view depends. You can't use an asterisk (*) when defining schema-bound views, so you must supply at least one column name in the view definition; I recommend the primary key. You can still use T-SQL to modify the table's schema, other than the columns in the schema-bound view, and most of us won't need to change the primary key. However, be careful when using Enterprise Manager to make schema changes; sometimes, the removal of SCHEMABINDING from the view is a side effect of changing the schema. Here's an example of the syntax you could use to manually create a schema-bound view for a table named Sales with a primary key named SalesPK:

CREATE VIEW dbo.Sales_   PreventDropViewWITH SCHEMABINDINGASSELECT SalesPK FROM dbo.Sales

However, creating a schema-bound view manually for each table would be time-consuming. To help you create a schema-bound view for all your production tables, I've written a stored procedure called sp_CreateSchemaboundViewToPreventTableDrop. This procedure requires a couple of other stored procedures, sp_GetPrimaryKeyList and sp_GenerateUniqueNameForNewObject, to create a uniquely named view for each table that uses their primary key. You can download the SchemaboundViews.sql script (see "Obtaining the Code," page 3, for instructions) to create these stored procedures in the master database. After creating the stored procedures, you can test them within your Pubs database. Begin by using sp_depends to see what views are currently on the authors table:

EXEC sp_depends authors

Then, use sp_CreateSchemaboundViewToPreventTableDrop to create a view on only one table:

EXEC sp_CreateSchemaboundViewTo   PreventTableDrop 'authors'

or on all the tables within your database:

EXEC sp_CreateSchemaboundViewToPreventTableDrop 'All'

Now, use sp_depends to check the dependencies again:

EXEC sp_depends authors

You'll see a new view called authors_PreventTableDropWithSBView. The authors table can't be dropped unless this view is removed or altered to exclude schemabinding. To remove this view, you can drop it with the DROP VIEW command. Or, you can use sp_DropAllSBPreventTableDropView with the view name to drop it. You can also use 'All' to remove all the PreventTableDropWithSBView views within your database:

EXEC sp_DropAllSBPreventTableDrop   Views 'authors_PreventTableDropWithSBView'--or EXEC sp_DropAllSBPreventTable   DropViews 'All'

Have fun!