Monitoring and Recording DDL changes on SQL 2005 (NARC)

Monitoring and recording DDL changes on SQL 2005 (NARC)

This article outlines the usage of server and database level DDL triggers in a SQL 2005 environment to monitor any

DDL changes. I affectionately call this T-SQL creation,"NARC", meaning "Non-Authorized Recorded Change".

The purpose of this code was to ensure changes made to the server configuration or database objects have been done by the people that should be doing

these changes. Furthermore, I can use this as my internal change management to review table or configuration changes the occurred in my SQL 2005 environment as

not only is the change recorded, but all the details of the change including the actual DDL used.

The process of implementing the NARC code is broken down into four steps:

Create two tables, "tblMonitorChange" and "tblMonitorSecurity", that will hold the DDL change data gathered in a database of your choosing to record the database changes. The database you create these tables in will require a matching code change in the DDL triggers
Create views that will filter out create statistics. The two views, vwMonitorChange and vwMonitorSecurity, are used
to filter out the events CREATE STATISTICS and ALTER INDEX for this example. Other events that the DBA deems not appropriate
to reviewing can be added/removed from the views as the DBA deems appropriate.
Create the Server Level DDL trigger "trgMonitorChangeSrv"
Create the Database Level DDL trigger "trgMonitorChange" for each database you wish to monitor on the server

STEP 1: Create tables "tblMonitorChange" and "tblMonitorSecurity". The "tblMonitorSecurity" table

holds all the changes that occur at the server level for the following DDL events

DDL_LOGIN_EVENTS
CREATE_DATABASE
DROP_DATABASE
ALTER_DATABASE

The "tblMonitorChange" table contains all the gathered data for the DDL_DATABASE_LEVEL_EVENTS on each database you have applied the database trigger code "tblMonitorChange" was deployed on.

/****** Object:  Table [dbo].[tblMonitorChange] ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
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]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[tblMonitorSecurity] ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
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]
GO
SET ANSI_PADDING OFF

STEP 2: Create views that will filter out create statistics. The two views, vwMonitorChange and vwMonitorSecurity, are used to filter out the events CREATE STATISTICS and ALTER INDEX for this example. Other events that the DBA deems not appropriate to reviewing can be added/removed from the views as the DBA deems appropriate. The attached SQL to this article contains the extended meta data for the views and is not shown in the code below.

/****** Object:  View [dbo].[vwMonitorChange] ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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
GO
/****** Object:  View [dbo].[vwMonitorSecurity]  ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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
GO

STEP 3: Create the Server Level DDL trigger. The trigger "trgMonitorChangeSrv" is a server level trigger which will

record the following DDL events:

DDL_LOGIN_EVENTS
CREATE_DATABASE
DROP_DATABASE
ALTER_DATABASE

Feel free to add/remove DDL events

at the server level as you see fit. For more information about DDL events, refer to the reference links at the bottom of the this article.

SIDEBAR: There are two ways to check if a SQL 2005 server has a server trigger already created.

In the SSMS GUI under Server Objects -->Triggers
T-SQL CODE:
```
SELECT * FROM sys.Server_Triggers
```

There are times when during a deployment of an application, you may not want to have the trigger on for whatever reason.

To Disable/Enable the server trigger use the T-SQL code syntax:

(ENABLE|DISABLE) TRIGGER trgMonitorChangeSrv ON ALL SERVER

NOTE: You will have to change the "dbname" in the trigger below to route the output data to the database you created the "tblMonitorSecurity"

table.

/****** Object:  DdlTrigger [trgMonitorChangeSrv] ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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 -- Change database name to database you are using
select @EventType, @SchemaName, @ObjectName, @ObjectType, getdate(), SUSER_SNAME(), CURRENT_USER, ORIGINAL_LOGIN(), @TSQL

STEP 4: Create the Database Level DDL trigger "trgMonitorChange". The trigger "trgMonitorChange"

is a database level trigger which will record the DDL_DATABASE_LEVEL_EVENTS, which is a parent to all these DDL events

DDL_TRIGGER_EVENTS
DDL_FUNCTION_EVENTS
DDL_SYNONYM_EVENTS
DDL_SSB_EVENTS
DDL_DATABASE_SECURITY_EVENTS
DDL_EVENT_NOTIFICATION_EVENTS
DDL_PROCEDURE_EVENTS
DDL_TABLE_VIEW_EVENTS
DDL_TYPE_EVENTS
DDL_XML_SCHEMA_COLLECTION_EVENTS
DDL_PARTITION_EVENTS
DDL_ASSEMBLY_EVENTS

Feel free to use a subset of these events if you do not wish to monitor all the DDL events on a user database. For more information about DDL events, refer to the reference links at the bottom of the this article.

OPTION: You can intall this trigger on the "Model" database so that every database created on the server

has this DDL trigger created by default.

SIDEBAR: There are two ways to check if a SQL 2005 database has a database trigger(s)

In the SSMS GUI under the database name --> Programmability --> Database triggers
T-SQL CODE (use in specific User database):
```
SELECT * FROM sys.Triggers
```

There are times when during a deployment of an application, you may not want to have the trigger on for whatever reason.

To Disable/Enable a database trigger, use the T-SQL code syntax on the database you wish to disable the trigger on:

Disable Database Trigger: (ENABLE|DISABLE) TRIGGER trgMonitorChange ON DATABASE

NOTE: You will have to change the "dbname" in the trigger below to route the output data to the database you created the "tblMonitorChange"

table.

/****** Object:  DdlTrigger [trgMonitorChange] ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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 -- Change database name to whatever you are using to record changes
      select @EventType, @SchemaName, @ObjectName, @ObjectType, getdate(), SUSER_SNAME(), CURRENT_USER, ORIGINAL_LOGIN(), @DBName, @TSQL
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

Commentary

This code was created so a DBA can have a mechanism to monitor the DDL changes to an environment. For my case, this code has been useful in environments where accounts have "sa" permissions due to legacy applications, required cross domain authentication, or whatever excuse someone has that their account needs "sa" level permissions. As much as I would like to limit control of the any DBA server to only a select chosen few, thatis not always the case. I had to come up with a "zero cost", "no new software",script deployable solution that would allow me to know who changed the database/server.

The classic rhetorical question when diagnosing a technicalissue "what changed on the server?", may now be possibly answered. Since some of my servers do not have Service Broker and Notification Services activated or installed, I had to come up with a solution that would work on my installed base of SQL 2005 as I originally intended to make this a Notification Services running asynchronously just a learning exercise.

Further development I planned to make:

Make a trigger on the tblMonitorChange and tblMonitorSecurity that would send a e-mail if hostname/domain
account other than my own tried to delete data as well as record the person trying to delete data.
Add more columns to record data for DDL events. I wanted to record just what information I really needed for
my environment, but your environment may benefit from other DDL XML columns that SQL 2005 tracks during the DDL process
Create a central database to use for all environmental changes within my environment.
The monitoring tables would become one repository that I could query for all databases/servers. This would require
the database/server triggers to use a linked server with perhaps synonyms for the linked server name for code
portability and deployment.

References:

Event Groups for Use with DDL Triggers: http://msdn2.microsoft.com/en-us/library/ms191441.aspx

Designing DDL Triggers: http://msdn2.microsoft.com/en-us/library/ms186406.aspx

DDL Events for Use with DDL Triggers: http://msdn2.microsoft.com/en-us/library/ms189871.aspx

Monitoring and Recording DDL changes on SQL 2005 (NARC)