In a SQL Server environment, it is important to monitor and record any Data Definition Language (DDL) changes that occur. These changes can include modifications to the server configuration or database objects. By implementing a system to track these changes, you can ensure that they are being made by authorized individuals and have a record of all the details of the change.
One approach to monitoring and recording DDL changes is by using server and database level DDL triggers. These triggers are pieces of T-SQL code that are executed automatically when certain DDL events occur. In this article, we will discuss the implementation of a system called “NARC” (Non-Authorized Recorded Change) that utilizes DDL triggers to monitor and record DDL changes on a SQL Server 2005 environment.
Step 1: Create Tables
The first step in implementing the NARC system is to create two tables: “tblMonitorChange” and “tblMonitorSecurity”. These tables will hold the DDL change data gathered in a database of your choosing to record the database changes. The “tblMonitorSecurity” table will hold changes that occur at the server level, such as DDL_LOGIN_EVENTS, CREATE_DATABASE, DROP_DATABASE, and ALTER_DATABASE. The “tblMonitorChange” table will contain data for the DDL_DATABASE_LEVEL_EVENTS on each database where the database trigger code is applied.
CREATE TABLE [dbo].[tblMonitorChange](
[EventType] [nvarchar](max) NULL,
[SchemaName] [nvarchar](max) NULL,
[ObjectName] [nvarchar](max) NULL,
[ObjectType] [nvarchar](max) NULL,
[EventDate] [datetime] NULL,
[SystemUser] [varchar](100) NULL,
[CurrentUser] [varchar](100) NULL,
[OriginalUser] [varchar](100) NULL,
[DatabaseName] [varchar](100) NULL,
[tsqlcode] [nvarchar](max) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tblMonitorSecurity](
[EventType] [nvarchar](max) NULL,
[SchemaName] [nvarchar](max) NULL,
[ObjectName] [varchar](100) NULL,
[ObjectType] [varchar](100) NULL,
[EventDate] [datetime] NULL,
[SystemUser] [varchar](100) NULL,
[CurrentUser] [varchar](100) NULL,
[OriginalUser] [varchar](100) NULL,
[tsqlcode] [nvarchar](max) NULL
) ON [PRIMARY]
Step 2: Create Views
Next, create views that will filter out certain events that you do not wish to review. In the example code, the views “vwMonitorChange” and “vwMonitorSecurity” filter out the events CREATE STATISTICS and ALTER INDEX. You can modify these views to include or exclude other events as needed.
CREATE VIEW [dbo].[vwMonitorChange]
AS
SELECT TOP (100) PERCENT EventType, SchemaName, ObjectName, ObjectType, EventDate, SystemUser, OriginalUser, DatabaseName, tsqlcode
FROM dbo.tblMonitorChange
WHERE (EventType NOT IN ('Create_Statistics', 'ALTER_INDEX'))
ORDER BY EventDate DESC
CREATE VIEW [dbo].[vwMonitorSecurity]
AS
SELECT TOP (100) PERCENT EventType, EventDate, SystemUser, OriginalUser, tsqlcode
FROM dbo.tblMonitorSecurity
WHERE (EventType NOT IN ('Create_Statistics', 'ALTER_INDEX'))
ORDER BY EventDate DESC
Step 3: Create Server Level DDL Trigger
The next step is to create a server level DDL trigger called “trgMonitorChangeSrv”. This trigger will record events such as DDL_LOGIN_EVENTS, CREATE_DATABASE, DROP_DATABASE, and ALTER_DATABASE. You can add or remove DDL events as needed for your environment.
Create TRIGGER [trgMonitorChangeSrv] ON ALL SERVER
FOR DDL_LOGIN_EVENTS, CREATE_DATABASE, DROP_DATABASE, ALTER_DATABASE
AS
set nocount on
declare @EventType NVARCHAR(MAX)
declare @SchemaName NVARCHAR(MAX)
declare @ObjectName varchar(100)
declare @ObjectType varchar(100)
DECLARE @Message VARCHAR(1000)
DECLARE @TSQL NVARCHAR(MAX)
SELECT
@EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)')
,@SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)')
,@TSQL = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
-- Is the default schema used
if @SchemaName = ' '
SELECT @SchemaName = default_schema_name
FROM sys.sysusers u
INNER JOIN sys.database_principals p
ON u.uid = p.principal_id
WHERE u.[name] = CURRENT_USER
insert into <dbname>.dbo.tblMonitorSecurity
select @EventType, @SchemaName, @ObjectName, @ObjectType, getdate(), SUSER_SNAME(), CURRENT_USER, ORIGINAL_LOGIN(), @TSQL
Step 4: Create Database Level DDL Trigger
Finally, create a database level DDL trigger called “trgMonitorChange”. This trigger will record the DDL_DATABASE_LEVEL_EVENTS, which includes various DDL events such as DDL_TRIGGER_EVENTS, DDL_FUNCTION_EVENTS, DDL_SYNONYM_EVENTS, and more. You can choose to monitor a subset of these events based on your requirements.
Create TRIGGER [trgMonitorChange] ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
set nocount on
declare @EventType nvarchar(MAX)
declare @SchemaName nvarchar(MAX)
declare @ObjectName nvarchar(MAX)
declare @ObjectType nvarchar(MAX)
DECLARE @DBName VARCHAR(100)
DECLARE @Message VARCHAR(1000)
DECLARE @TSQL NVARCHAR(MAX)
SELECT
@EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)')
,@SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)')
,@ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')
,@ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)')
,@DBName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)')
,@TSQL = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
-- Is the default schema used
if @SchemaName = ' '
SELECT @SchemaName = default_schema_name
FROM sys.sysusers u
INNER JOIN sys.database_principals p
ON u.uid = p.principal_id
WHERE u.[name] = CURRENT_USER
insert into <dbname>.dbo.tblMonitorChange
select @EventType, @SchemaName, @ObjectName, @ObjectType, getdate(), SUSER_SNAME(), CURRENT_USER, ORIGINAL_LOGIN(), @DBName, @TSQL
By following these four steps, you can implement a system to monitor and record DDL changes on your SQL Server environment. This system can help you track changes made to the server configuration or database objects, ensuring that they are done by authorized individuals. Additionally, you will have a record of all the details of the change, including the actual DDL used.
Further development of this system could include adding triggers on the monitoring tables to send email notifications when unauthorized changes are attempted, recording additional data for DDL events, and creating a central database to store all environmental changes for easy querying.
References: