Prevent Tables From Being Created, Dropped, Or Altered In SQL Server

Introduction

In this tutorial, I am going to explain the method to prevent tables from being created, altered, or dropped in SQL Server. This detailed article will cover the following topics:

1. Introduction
2. Introduction to DDL Triggers
3. DDL Trigger Scope
4. Prevent Table from being Created, Altered, or Dopped in SQL Server
5. Difference Between DDL and DML Triggers
6. Conclusion

First, let's create a database with some tables containing some dummy data. Here, I am providing you with the database along with the tables containing the records, on which I am showing you the various examples. Let's see.

CREATE DATABASE OnkarSharma_Security
PRINT 'New Database ''OnkarSharma_Security'' Created'
GO

USE [OnkarSharma_Security]
GO

CREATE TABLE [dbo].[Employee] (
	EmployeeID INT IDENTITY (31100,1),
	EmployerID BIGINT NOT NULL DEFAULT 228866,
	FirstName VARCHAR(50) NOT NULL,
	LastName VARCHAR(50) NOT NULL,
	Email VARCHAR(255) NOT NULL UNIQUE,
	DepartmentID VARCHAR(100) NOT NULL,
	Age INT  NOT NULL,
	GrossSalary BIGINT NOT NULL,
	PerformanceBonus BIGINT,
	ContactNo VARCHAR(25),
	PRIMARY KEY (EmployeeID)
);

CREATE TABLE [dbo].[tbl_Orders] (
	OrderId INT IDENTITY (108, 1) PRIMARY KEY,
	FoodieID INT,
	OrderStatus TINYINT NOT NULL, -- ==>> OrderStatus: 4: Cancelled; 3: Pending; 2: Processing; 1: Completed
	OrderDate DATE NOT NULL,
	ShippedDate DATE,
	RestaurantId INT NOT NULL,
);

CREATE TABLE [dbo].[tbl_OrderItems](
	OrderId INT NOT NULL,
	ItemId INT,
	MenuId INT NOT NULL,
	Quantity INT NOT NULL,
	Price DECIMAL(6, 2) NOT NULL,
	Discount DECIMAL(5, 2) NOT NULL DEFAULT 0,
	PRIMARY KEY (ItemId)
);

CREATE TABLE [dbo].[tbl_Menu] (
	MenuId INT IDENTITY (81, 1) PRIMARY KEY,
	FoodCategoryID INT NOT NULL,
	FoodName VARCHAR (255) NOT NULL,
	TypeofFood VARCHAR (100) NOT NULL,
	Price DECIMAL(6, 2) NOT NULL
);

Let's check our following tables by using the following queries.

1) To get the data from the "Employee" table, use the following query.

SELECT * FROM OnkarSharma_Security..Employee

2) To get the data from the "tbl_Menu" table, use the following query.

SELECT * FROM OnkarSharma_Security..tbl_Menu

DDL Triggers

SQL Server DDL triggers fire in response to various DDL (Data Definition Language) events. These events mainly correspond to T-SQL statements that begin with one of the following keywords CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS. DDL triggers can be used for the following cases:

1. Record changes or events in the database schema.
2. Prevent some changes to the database schema.
3. Respond to changes in the database schema.

Key Points

• DDL Triggers was introduced in SQL Server 2005.
• Multiple triggers can be created for each DDL event.
• DDL triggers can be used to restrict DDL operations.
• DDL triggers are not triggered by events affecting local or global temporary tables.
• DDL triggers do not create the special inserted and deleted tables.

Syntax

CREATE TRIGGER <trigger_name>
ON [ALL SERVER | DATABASE]
WITH [trigger_option]
FOR <event_type(s)>
AS
BEGIN
<trigger_body>
END

DDL Trigger Scope

The scope of the trigger depends on the event. DDL triggers can be created either on a specific database or on the server. DDL triggers can be either in database or server scope.

• DDL triggers with SERVER level scope are triggered in response to DDL events having SERVER Scope, such as CREATE_DATABASE, DROP _DATABASE, CREATE _LOGIN, ALTER _LOGIN, CREATE_LINKED_SERVER, and so on.
• DDL triggers with DATABASE Scope fire in response to DDL events with DATABASE Scope, such as CREATE _TABLE, ALTER _TABLE, DROP_TABLE, CREATE _PROCEDURE, ALTER _PROCEDURE, CREATE _FUNCTION, ALTER _FUNCTION, CREATE _VIEW, ALTER_VIEW, and so on.

Note

To find the,

• Database Scoped Triggers, Navigate: <Database_Name> > Programmability > Database Triggers. (Be sure to refresh, Database Triggers).
• Server Scoped Triggers, Navigate: Server Objects > Triggers (Be sure to refresh, Triggers).

ENABLE Trigger

The ENABLE TRIGGER statement enables a trigger to be fired whenever an event occurs.

Syntax

ENABLE TRIGGER <trigger_name>
ON [ALL SERVER | DATABASE | Object_Name]

DISABLE Trigger

The DISABLE TRIGGER statement deactivates a trigger so that it cannot be fired. Sometimes, we need to disable it.

Syntax

DISABLE TRIGGER <trigger_name>
ON [ALL SERVER | DATABASE | Object_Name]

DROP Trigger

The DROP TRIGGER statement is used to drop one or more triggers from the database or server.

Syntax

DROP TRIGGER [IF EXISTS] <trigger_name>
ON [ALL SERVER | DATABASE]

Prevent Tables from being Created, Altered, or Dropped in SQL Server

Follow the given steps carefully to prevent tables from being created, altered, or dropped in SQL Server. Let's see.

A) Prevent Tables From Being Created

Step 1

Execute the following trigger statement to create a DDL Trigger.

IF EXISTS(SELECT * FROM sys.triggers WHERE NAME = N'Tr_CreateTableSecurity' AND PARENT_CLASS_DESC = N'DATABASE')
BEGIN
	DROP TRIGGER Tr_CreateTableSecurity
	ON DATABASE
END
GO

CREATE TRIGGER Tr_CreateTableSecurity
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
	PRINT 'You should ask your DBA or disable the trigger ''Tr_CreateTableSecurity'' to create the table!' 
	ROLLBACK TRANSACTION
END
GO

ENABLE TRIGGER Tr_CreateTableSecurity
ON DATABASE
GO

Note
You can find the Database Scoped Triggers in, <Database_Name> > Programmability > Database Triggers. (Be sure to refresh, Database Triggers).

Step 2

Now, try to create the table by executing the following statement.

CREATE TABLE [dbo].[tbl_Customer] (
	CustomerId INT IDENTITY (504, 1),
	CustomerFullName VARCHAR(100) NOT NULL,
	CustomerAddress VARCHAR(250) NOT NULL,
	EmailId VARCHAR(50) UNIQUE,
	ContactNo BIGINT NOT NULL,
	PRIMARY KEY (CustomerId)
);

Step 3

From the above result, we can clearly see that the "Tr_CreateTableSecurity" trigger is preventing the user from creating the table. Now, disable the trigger by executing the following query.

DISABLE TRIGGER Tr_CreateTableSecurity
ON DATABASE
GO

Step 4

Now, execute the "Step 2" query again. And, to confirm, execute the following query.

SELECT * FROM OnkarSharma_Security..tbl_Customer;

B) Prevent Tables From Being Altered

Step 1

Execute the following trigger statement to create a DDL Trigger.

IF EXISTS(SELECT * FROM sys.triggers WHERE NAME = N'Tr_AlterTableSecurity' AND PARENT_CLASS_DESC = N'DATABASE')
BEGIN
	DROP TRIGGER Tr_AlterTableSecurity
	ON DATABASE
END
GO

CREATE TRIGGER Tr_AlterTableSecurity
ON DATABASE
FOR ALTER_TABLE
AS
BEGIN
	PRINT 'You should ask your DBA or disable the trigger ''Tr_AlterTableSecurity'' to alter the table!' 
	ROLLBACK TRANSACTION
END
GO

ENABLE TRIGGER Tr_AlterTableSecurity
ON DATABASE
GO

Step 2

Now, try adding a new column "DOB" to the "Employee" table.

ALTER TABLE Employee
ADD DOB BIGINT
GO

Step 3

From the above result, we can clearly see that the "Tr_AlterTableSecurity" trigger is preventing the user from adding a new column to the table. Now, disable the trigger by executing the following query.

DISABLE TRIGGER Tr_AlterTableSecurity
ON DATABASE
GO

Step 4

Now, execute the "Step 2" query again. After that, execute the following query to see the result.

SELECT * FROM OnkarSharma_Security..Employee;

C) Prevent Tables From Being Dropped

Step 1

Execute the following trigger statement to create a DDL Trigger.

IF EXISTS(SELECT * FROM sys.triggers WHERE NAME = N'Tr_DropTableSecurity' AND PARENT_CLASS_DESC = N'DATABASE')
BEGIN
	DROP TRIGGER Tr_DropTableSecurity
	ON DATABASE
END
GO

CREATE TRIGGER Tr_DropTableSecurity
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
	PRINT 'You should ask your DBA or disable the trigger ''Tr_DropTableSecurity'' to drop the table!' 
	ROLLBACK TRANSACTION
END
GO

ENABLE TRIGGER Tr_DropTableSecurity
ON DATABASE
GO

Step 2

Now, try to drop the table.

DROP TABLE tbl_Menu;

Step 3

From the above result, we can clearly see that the "Tr_DropTableSecurity" trigger is preventing the user from dropping the table. Now, disable the trigger by executing the following query.

DISABLE TRIGGER Tr_DropTableSecurity
ON DATABASE
GO

Step 4

Again, try to drop the table. After that, execute the following query to see the result.

SELECT * FROM OnkarSharma_Security..tbl_Menu;

Difference Between DDL and DML Triggers

Now, let's look at the difference between DDL and DML Triggers in SQL Server. Let's see.

S.No.	Key Points	DDL Triggers	DML Triggers
1	Definition	DDL triggers fire in response to various DDL events.	DML triggers fire in response to various DML events.
2	History	DDL Triggers was introduced in SQL Server 2005.	---
3	Used Special Tables?	No	Yes
4	Create Multiple triggers on an object?	Yes	Yes
5	Usage	Can be used to audit and prevent changes in the database schema.	Can be used to enforce business rules and data integrity.

See you in the next article, till then take care and be happy learning.

You may also visit my other articles,

• Difference Between Delete, Truncate, And Drop Statements In SQL Server
• Difference Between HAVING And WHERE Clause In SQL Server
• How To Generate Scripts For Database Objects In SQL Server
• Different Ways To Handle NULL In SQL Server
• IIF Function In SQL Server

Conclusion

In this article, we have discussed the method to prevent the tables from being created, altered, or dropped in SQL Server with various examples.

I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about SQL Server.

Thanks for reading.