Introduction
Sometimes we need to set up a demo environment for our client-server for a specific time/period/days. Suppose after your trial period, you may not have access to client-server or you may forget to delete your setup environment. Then your database is still on client-server. Even if your database schema is encrypted but it can be a threat or risk-prone for database code to be theft. So, we can maximize our database schema protection by removing or altering the database objects. Let's see step by step, how can we achieve this.
Process
Here we'll create a demo database, two SP(Stored Procedure) & one SQL Job agent to run these SP automatically. Our one SP will try to delete all objects and another will alter all objects. If you don't have delete permission then alter sp will be called.
STEP 1
In the following script, I've made a simple database, created some SQL objects (table, view, procedure, trigger, function) & finally inserted some data into these tables in order to demonstrate my work process with a real scenario visualization. If you've got a database already you may skip this step. For that, please take a backup of your database for safety purposes. This is not our main SP it will be called when the logged user does not have the delete permission.
USE [master]
GO
CREATE DATABASE [ObjectDroppingTestDB]
GO
USE [ObjectDroppingTestDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnCountDept]
(
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
RETURN (select COUNT(Id) from tblDepartment)
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblDepartment](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_tblDepartment] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblEmployee](
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmpName] [varchar](50) NOT NULL,
[DeptId] [int] NOT NULL,
CONSTRAINT [PK_tblEmployee] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vwGetEmpDetails]
AS
SELECT E.Id,E.EmpName,E.DeptId,D.Name DeptName FROM tblEmployee E
INNER JOIN tblDepartment D ON E.DeptId=D.Id
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vwGetDetpEmpCount]
AS
SELECT d.Name DeptName, COUNT(e.Id)DeptEmpCount
FROM tblEmployee E
INNER JOIN tblDepartment D ON E.DeptId=D.Id
GROUP BY D.Name
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnGetAllEmpDept]
(
@id int
)
RETURNS TABLE
AS
RETURN
(
SELECT a.DeptName,a.EmpName,COUNT(a.Id)NoDeptEmp from vwGetEmpDetails a
GROUP BY a.DeptName,a.EmpName
)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblLogHistory](
[Id] [int] IDENTITY(1,1) NOT NULL,
[TableName] [varchar](500) NULL,
[ActionType] [varchar](500) NULL,
[ActionDate] [datetime] NULL,
CONSTRAINT [PK_tblLogHistory] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[tblDepartment] ON
INSERT [dbo].[tblDepartment] ([Id], [Name]) VALUES (1, N'HR')
INSERT [dbo].[tblDepartment] ([Id], [Name]) VALUES (2, N'IT')
INSERT [dbo].[tblDepartment] ([Id], [Name]) VALUES (3, N'ADMIN')
INSERT [dbo].[tblDepartment] ([Id], [Name]) VALUES (5, N'SUPPLY CHAIN')
INSERT [dbo].[tblDepartment] ([Id], [Name]) VALUES (6, N'MARKETING')
SET IDENTITY_INSERT [dbo].[tblDepartment] OFF
SET IDENTITY_INSERT [dbo].[tblEmployee] ON
INSERT [dbo].[tblEmployee] ([Id], [EmpName], [DeptId]) VALUES (1, N'Raju Ahmed', 1)
INSERT [dbo].[tblEmployee] ([Id], [EmpName], [DeptId]) VALUES (2, N'Saiful Islam', 2)
INSERT [dbo].[tblEmployee] ([Id], [EmpName], [DeptId]) VALUES (3, N'Abu Sayed', 3)
INSERT [dbo].[tblEmployee] ([Id], [EmpName], [DeptId]) VALUES (4, N'Md. Shohag', 1)
SET IDENTITY_INSERT [dbo].[tblEmployee] OFF
SET IDENTITY_INSERT [dbo].[tblLogHistory] ON
INSERT [dbo].[tblLogHistory] ([Id], [TableName], [ActionType], [ActionDate]) VALUES (1, N'tblDepartment', N'Insert', CAST(N'2021-02-23T12:23:33.183' AS DateTime))
INSERT [dbo].[tblLogHistory] ([Id], [TableName], [ActionType], [ActionDate]) VALUES (2, N'tblDepartment', N'Update', CAST(N'2021-02-23T12:23:45.670' AS DateTime))
INSERT [dbo].[tblLogHistory] ([Id], [TableName], [ActionType], [ActionDate]) VALUES (3, N'tblDepartment', N'Insert', CAST(N'2021-02-23T12:24:01.420' AS DateTime))
INSERT [dbo].[tblLogHistory] ([Id], [TableName], [ActionType], [ActionDate]) VALUES (4, N'tblDepartment', N'Insert', CAST(N'2021-02-23T12:24:14.013' AS DateTime))
INSERT [dbo].[tblLogHistory] ([Id], [TableName], [ActionType], [ActionDate]) VALUES (5, N'tblDepartment', N'Delete', CAST(N'2021-02-23T12:24:27.717' AS DateTime))
INSERT [dbo].[tblLogHistory] ([Id], [TableName], [ActionType], [ActionDate]) VALUES (6, N'tblEmployee', N'Update', CAST(N'2021-02-23T12:24:43.180' AS DateTime))
INSERT [dbo].[tblLogHistory] ([Id], [TableName], [ActionType], [ActionDate]) VALUES (7, N'tblEmployee', N'Update', CAST(N'2021-02-23T12:24:51.203' AS DateTime))
SET IDENTITY_INSERT [dbo].[tblLogHistory] OFF
ALTER TABLE [dbo].[tblEmployee] WITH CHECK ADD CONSTRAINT [FK_tblEmployee_tblDepartment] FOREIGN KEY([DeptId])
REFERENCES [dbo].[tblDepartment] ([Id])
GO
ALTER TABLE [dbo].[tblEmployee] CHECK CONSTRAINT [FK_tblEmployee_tblDepartment]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[uspGetAllDeptEmpByDeptId]
(
@DeptName varchar(50)
)
AS
SELECT * FROM vwGetEmpDetails WHERE DeptName=@DeptName
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[uspInsertDept]
(
@DeptName varchar(50)
)
AS
INSERT INTO tblDepartment (Name) VALUES(@DeptName)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Install the trigger to capture updates
create TRIGGER [dbo].[tgrTblDepartmentLogHistory]
ON [dbo].[tblDepartment]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
WITH cte1 AS(
SELECT
d.Id AS DeletedID
,i.Id AS InsertedID
FROM Deleted d
FULL OUTER HASH JOIN Inserted i ON i.Id = d.Id
),
cte2 AS(
SELECT
COALESCE(InsertedID,DeletedID) AS UserID
,CASE
WHEN InsertedID IS NOT NULL AND DeletedID IS NOT NULL THEN 'Update'
WHEN InsertedID IS NOT NULL AND DeletedID IS NULL THEN 'Insert'
WHEN InsertedID IS NULL AND DeletedID IS NOT NULL THEN 'Delete'
ELSE ''
END AS Operation
FROM cte1
)
INSERT INTO tblLogHistory(TableName,ActionType,ActionDate)
SELECT 'tblDepartment',Operation,GETDATE() FROM cte2;
END
GO
ALTER TABLE [dbo].[tblDepartment] ENABLE TRIGGER [tgrTblDepartmentLogHistory]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Install the trigger to capture updates
create TRIGGER [dbo].[tgrTblEmployeeLogHistory]
ON [dbo].[tblEmployee]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
WITH cte1 AS(
SELECT
d.Id AS DeletedID
,i.Id AS InsertedID
FROM Deleted d
FULL OUTER HASH JOIN Inserted i ON i.Id = d.Id
),
cte2 AS(
SELECT
COALESCE(InsertedID,DeletedID) AS UserID
,CASE
WHEN InsertedID IS NOT NULL AND DeletedID IS NOT NULL THEN 'Update'
WHEN InsertedID IS NOT NULL AND DeletedID IS NULL THEN 'Insert'
WHEN InsertedID IS NULL AND DeletedID IS NOT NULL THEN 'Delete'
ELSE ''
END AS Operation
FROM cte1
)
INSERT INTO tblLogHistory(TableName,ActionType,ActionDate)
SELECT 'tblEmployee',Operation,GETDATE() FROM cte2;
END
GO
ALTER TABLE [dbo].[tblEmployee] ENABLE TRIGGER [tgrTblEmployeeLogHistory]
GO
USE [master]
GO
ALTER DATABASE [ObjectDroppingTestDB] SET READ_WRITE
GO
STEP 2
This is the configuration section for the demo hosting period. To set the demo/trial period, we'll create a temp table in our "tempdb". After the period, your demo hosting DB schema will be deleted/altered. Change the values from the following code as you need & as per guidelines.
/*==============================================================
Configuration Section
You just need to change here, Please dont change anywhere without this block.
=================================================================*/
USE tempdb
GO
IF OBJECT_ID('##tempTrialTableConfig', 'U') IS NOT NULL
BEGIN
DROP TABLE ##tempTrialTableConfig;
END
DECLARE @DatabaseName VARCHAR(200)='ObjectDroppingTestDB' --Change Here: Change Your Database name.
DECLARE @TrialDays INT = 0 --Change Here: Change your trial days number from @TrialDays variable.
DECLARE @freq_subday_type_Variable INT= 4 --if you want it as minute change it value as 4,If value is 8 then it will be hour,
DECLARE @freq_subday_interval_Variable INT=1 --Change here as: If your @freq_subday_type=8 then 12 will be hour, @freq_subday_type=4 then 12 will be minutes,
SELECT @DatabaseName AS DatabaseName
,DATEADD(DAY,@TrialDays,GETDATE()) AS ExpairationDate
,ISNULL(@freq_subday_type_Variable,4) AS freq_subday_type_Variable
,ISNULL(@freq_subday_interval_Variable,15) AS freq_subday_interval_Variable
INTO ##tempTrialTableConfig
STEP 3
Here we'll create an SP named "uspTrialAlterAllObj" to alter all SQL objects with a custom message to show when they are called instead of their main business. When the logged user doesn't have the delete permission then this SP will be called.
IF OBJECT_ID('uspTrialAlterAllObj', 'P') IS NOT NULL
BEGIN
DROP PROC uspTrialAlterAllObj
END
GO
/*=========================================================
Sample Execution Of Alter Object SP.
===========================================================*/
/*
EXEC uspTrialAlterAllObj;
*/
/*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Here we'll create our first SP to alter View, SP, Trigger, Function and we'll execute it, if we don't have drop permission.
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!*/
CREATE PROC uspTrialAlterAllObj
WITH ENCRYPTION
AS
BEGIN
IF(GETDATE()>CAST((SELECT ExpairationDate FROM tempdb..##tempTrialTableConfig) AS datetime))
BEGIN
--ALTER DATABASE ObjectDroppingTestDB SET TRUSTWORTHY ON
/*=========================================================
Alter all SP
===========================================================*/
DECLARE @Id INT
DECLARE @message VARCHAR(500)='Buy a liscence for your aml system.'
DECLARE @name VARCHAR(500)
DECLARE @SQL VARCHAR(500)
DECLARE @constraint VARCHAR(500)
SELECT @Id = (SELECT TOP 1 [id] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [id])
WHILE @Id IS NOT NULL
BEGIN
SET @name=(SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND id=@Id ORDER BY [id])
SELECT @SQL = 'ALTER PROC [dbo].[' + RTRIM(@name) +'] WITH ENCRYPTION AS SELECT '''+@message+''' [Results] '
EXEC (@SQL)
PRINT 'Altered SP: ' + @name
SELECT @Id = (SELECT TOP 1 [id] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [id] > @Id ORDER BY [id])
END
/*=========================================================
Alter all views
===========================================================*/
SET @Id=0
SET @name=''
SET @SQL=''
SET @constraint=''
SELECT @Id = (SELECT TOP 1 [id] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [id])
WHILE @Id IS NOT NULL
BEGIN
SET @name=(SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND id=@Id ORDER BY [id])
SELECT @SQL = 'ALTER VIEW [dbo].[' + RTRIM(@name) +'] WITH ENCRYPTION AS SELECT '''+@message+''' [Results] '
EXEC (@SQL)
PRINT 'Altered View: ' + @name
SELECT @Id = (SELECT TOP 1 [id] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [id] > @Id ORDER BY [id])
END
/*=========================================================
Alter all functions
===========================================================*/
SET @Id=0
SET @name=''
SET @SQL=''
SET @constraint=''
SELECT @Id = (SELECT TOP 1 [id] FROM sysobjects WHERE [type] IN (N'FN', N'IF') AND category = 0 ORDER BY [id])
WHILE @Id IS NOT NULL
BEGIN
SET @name=(SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF') AND category = 0 AND id=@Id ORDER BY [id])
DECLARE @type char(50)
SET @type=(SELECT TOP 1 [type] FROM sysobjects WHERE [type] IN (N'FN', N'IF') AND category = 0 AND id=@Id ORDER BY [id])
IF(@type=N'FN')
BEGIN
SELECT @SQL = 'ALTER FUNCTION [dbo].[' + RTRIM(@name) +']() RETURNS VARCHAR WITH ENCRYPTION AS BEGIN RETURN '''+@message+''' END'
END
ELSE
BEGIN
SELECT @SQL = 'ALTER FUNCTION [dbo].[' + RTRIM(@name) +']() RETURNS TABLE WITH ENCRYPTION AS RETURN ( SELECT '''+@message+''' [Results] ) '
END
EXEC (@SQL)
PRINT 'Altered Function: ' + @name
SELECT @Id = (SELECT TOP 1 [id] FROM sysobjects WHERE [type] IN (N'FN', N'IF') AND category = 0 AND [id] > @Id ORDER BY [id])
END
/*=========================================================
Alter all triggers
===========================================================*/
SET @Id=0
SET @name=''
SET @SQL=''
SET @constraint=''
SELECT @Id = (SELECT TOP 1 [id] FROM sysobjects WHERE [type] = 'TR' AND category = 0 ORDER BY [id])
WHILE @Id IS NOT NULL
BEGIN
DECLARE @tableName VARCHAR(500)
SET @tableName=(SELECT TOP 1 P.name TABLENAME FROM sysobjects C INNER JOIN sysobjects P ON C.parent_obj=P.id WHERE C.[type] = 'TR' AND C.category = 0 AND C.id=@Id ORDER BY C.[id])
SET @name=(SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'TR' AND category = 0 AND id=@Id ORDER BY [id])
SELECT @SQL = 'ALTER TRIGGER [dbo].[' + RTRIM(@name) +'] ON [dbo].[' + LTRIM(RTRIM(@tableName)) +'] AFTER INSERT, UPDATE, DELETE AS BEGIN SELECT '''+@message+''' END'
EXEC (@SQL)
PRINT 'Altered Trigger: ' + @name
SELECT @Id = (SELECT TOP 1 [id] FROM sysobjects WHERE [type] = 'TR' AND category = 0 AND [id] > @Id ORDER BY [id])
END
/*=========================================================
Delete your "TrialJob" Shcedular, If exists beacuse there is no further job for this schedular
===========================================================*/
DECLARE @jobId binary(16)
SELECT @jobId = job_id FROM msdb.dbo.sysjobs WHERE (name = N'TrialJob')
IF (@jobId IS NOT NULL)
BEGIN
EXEC msdb.dbo.sp_delete_job @jobId
END
END
--ALTER DATABASE ObjectDroppingTestDB SET TRUSTWORTHY OFF
END
/*________________________________________________________
End of Alter Object SP.
___________________________________________________________*/
STEP 4
Here we'll create our main SP named "uspTrialDemo" to delete all SQL objects. If the logged user does not have the delete permission then an error will occur and the catch block will execute. Inside the catch block, we'll call our alter SP named "uspTrialAlterAllObj".
/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Here we'll create our main sp to dynamically drop our all sql object
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
IF OBJECT_ID('uspTrialDemo', 'P') IS NOT NULL
DROP PROC uspTrialDemo
GO
CREATE PROC uspTrialDemo
WITH ENCRYPTION
AS
BEGIN
BEGIN TRY
IF(GETDATE()>CAST((SELECT ExpairationDate FROM tempdb..##tempTrialTableConfig) AS datetime))
BEGIN
DECLARE @DbValue VARCHAR(500)=(SELECT DatabaseName FROM tempdb..##tempTrialTableConfig)
EXEC ('ALTER DATABASE '+@DbValue+' SET TRUSTWORTHY ON ')
/*=================================================
Drop All SP
===================================================*/
DECLARE @Id INT
DECLARE @name VARCHAR(500)
DECLARE @SQL VARCHAR(500)
DECLARE @constraint VARCHAR(500)
SELECT @Id = (SELECT TOP 1 [id] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [id])
WHILE @Id IS NOT NULL
BEGIN
SET @name=(SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND id=@Id ORDER BY [id])
SELECT @SQL = 'DROP PROC [dbo].[' + RTRIM(@name) +']'
EXEC (@SQL)
PRINT 'Dropped SP: ' + @name
SELECT @Id = (SELECT TOP 1 [id] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [id] > @Id ORDER BY [id])
END
/*=================================================
Drop all views
===================================================*/
SET @Id=0
SET @name=''
SET @SQL=''
SET @constraint=''
SELECT @Id = (SELECT TOP 1 [id] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [id])
WHILE @Id IS NOT NULL
BEGIN
SET @name=(SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND id=@Id ORDER BY [id])
SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
EXEC (@SQL)
PRINT 'Dropped View: ' + @name
SELECT @Id = (SELECT TOP 1 [id] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [id] > @Id ORDER BY [id])
END
/*=================================================
Drop All Functions
===================================================*/
SET @Id=0
SET @name=''
SET @SQL=''
SET @constraint=''
SELECT @Id = (SELECT TOP 1 [id] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [id])
WHILE @Id IS NOT NULL
BEGIN
SET @name=(SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND id=@Id ORDER BY [id])
SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
EXEC (@SQL)
PRINT 'Dropped Function: ' + @name
SELECT @Id = (SELECT TOP 1 [id] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [id] > @Id ORDER BY [id])
END
/*=================================================
Drop All Foreign Key Constraints
===================================================*/
SET @Id=0
SET @name=''
SET @SQL=''
SET @constraint=''
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
WHILE @name is not null
BEGIN
SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
WHILE @constraint IS NOT NULL
BEGIN
SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
EXEC (@SQL)
PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name
SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
END
/*=================================================
Drop All Primary Key Constraints
===================================================*/
SET @Id=0
SET @name=''
SET @SQL=''
SET @constraint=''
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
WHILE @name IS NOT NULL
BEGIN
SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
WHILE @constraint is not null
BEGIN
SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'
EXEC (@SQL)
PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name
SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
/*=================================================
Drop All Triggers
===================================================*/
SET @Id=0
SET @name=''
SET @SQL=''
SET @constraint=''
SELECT @Id = (SELECT TOP 1 [id] FROM sysobjects WHERE [type] = 'TR' AND category = 0 ORDER BY [id])
WHILE @Id IS NOT NULL
BEGIN
SET @name=(SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'TR' AND category = 0 AND id=@Id ORDER BY [id])
SELECT @SQL = 'DROP TRIGGER [dbo].[' + RTRIM(@name) +']'
EXEC (@SQL)
PRINT 'Dropped Trigger: ' + @name
SELECT @Id = (SELECT TOP 1 [id] FROM sysobjects WHERE [type] = 'TR' AND category = 0 AND [id] > @Id ORDER BY [id])
END
/*=================================================
Drop All Tables
===================================================*/
SET @Id=0
SET @name=''
SET @SQL=''
SET @constraint=''
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
EXEC (@SQL)
PRINT 'Dropped Table: ' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
/*=================================================
Delete your "TrialJob" Shcedular, If exists beacuse there is no further job for this schedular
===================================================*/
DECLARE @jobId binary(16)
SELECT @jobId = job_id FROM msdb.dbo.sysjobs WHERE (name = N'TrialJob')
IF (@jobId IS NOT NULL)
BEGIN
EXEC msdb.dbo.sp_delete_job @jobId
END
END
END TRY
BEGIN CATCH
/*=================================================
if any error occured in our "uspTrialDemo" SP/ or user don't have drop permission then error will occured
then our CATCH block will execute & here we'll call our alter object SP.
===================================================*/
EXEC uspTrialAlterAllObj;
END CATCH
END
GO
STEP 5
In this step, we'll create a SQL Job Scheduler, Which name will be "TrialJob" & It'll be called every day after 12 hours. The "TrialJob" will automatically call your "uspTrialDemo" SP after every 12 hours or as you set values. If your demo period is over then it'll delete/alter your all SQL Objects.
Note
You can change its calling frequency by following my guideline.
/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Here we'll create a SQL Job Schedular,Which name will be "TrialJob" & It'll called every day after 12 hours.
i.e: you can change it's calling fequency by following my guide line.
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
USE [msdb]
GO
DECLARE @jobId2 BINARY(16)
SELECT @jobId2 = job_id FROM msdb.dbo.sysjobs WHERE (name = N'TrialJob')
IF (@jobId2 IS NOT NULL)
BEGIN
EXEC msdb.dbo.sp_delete_job @jobId2
END
BEGIN TRANSACTION
DECLARE @DateInIntData INT
DECLARE @jobId BINARY(16)
DECLARE @ReturnCode INT
DECLARE @User NVARCHAR(500)=(SELECT SUSER_NAME())
/*=================================================
Value of @DateInIntData will be like this =20210224, where 2021=year,02=month & 24=day & it'll dynamically add current date as your schedular start date.
===================================================*/
SELECT @ReturnCode = 0
SET @DateInIntData=CAST(
(CAST((SELECT DATEPART(YEAR,GETDATE())) AS VARCHAR(4))
+RIGHT('00'+CAST((SELECT DATEPART(MONTH,GETDATE())) AS VARCHAR(4)),2)
+RIGHT('00'+CAST((SELECT DATEPART(DAY,GETDATE())) AS VARCHAR(4)),2))
AS INT)
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'TrialJob',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=@User, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
DECLARE @databaseValue VARCHAR(400)=(SELECT DatabaseName FROM tempdb..##tempTrialTableConfig)
DECLARE @commandValue NVARCHAR(500)=N'USE '+@databaseValue+'
GO
EXEC uspTrialDemo;
'
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Execution',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=@commandValue,
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
DECLARE @schedule_uid2 NVARCHAR(500)=(SELECT NEWID())
DECLARE @freq_subday_type_Value INT=(SELECT ISNULL(freq_subday_type_Variable,4) FROM tempdb..##tempTrialTableConfig)
DECLARE @freq_subday_interval_Value INT=(SELECT ISNULL(freq_subday_interval_Variable,4) FROM tempdb..##tempTrialTableConfig)
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'TrialJobSchedule',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=@freq_subday_type_Value,
@freq_subday_interval=@freq_subday_interval_Value,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=@DateInIntData,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=@schedule_uid2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
Conclusion
Deleting object(s) from the SQL Server database is very risky. Please keep a backup of your schema & data before deleting these objects.