SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz Development Resources
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2017
download SQL Server 2016
download SQL Server 2014



How to Prevent DROP Table using SQL Server View With SchemaBinding

SQL programmers can create SQL Server database VIEW objects using With SchemaBinding option.
To create view with SchemaBinding enables sql developers create database view objects that will prevent changes in the dependent database objects.
This will result in more stable sql View objects.

SQL Server database administrators can use Create View With SchemaBinding syntax to prevent unauthorized Drop Table commands.
This is an alternative method of preventing database tables to using DDL Trigger against DROP Table in SQL Server.

For example, as a SQL Server administrator if you don't want someone to drop table SQLTable1 and drop table SQLTable2, you can create a view with schemabinding option.
In the SELECT statement of the new sql view, you can select rows from those sql tables that you want to prevent them from accidental DROP.

CREATE VIEW v_PreventDropTable WITH SCHEMABINDING
AS

SELECT TOP 1 null colname FROM dbo.SQLTable1
UNION ALL
SELECT TOP 1 null FROM dbo.SQLTable2
GO

In order to manage all database tables in single SQL View, you can "SELECT TOP 1 NULL" row from each database table. And you can use UNION ALL to manage this in a single SELECT.





Let's now make a sql example, how will Create View With SchemaBinding will prevent sql tables from an unauthorized Drop Table statement.
Create a dummy sql database table named SQLTable1.
Then execute following sql drop table command.

DROP TABLE SQLTable1

The output of the above t-sql DROP Table command will result like a following result.

Msg 3729, Level 16, State 1, Line 1
Cannot DROP TABLE 'SQLTable1' because it is being referenced by object 'v_PreventDropTable'.






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems