Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Monitoring and Recording DDL changes on SQL 2005 (NARC)

By James Greaves,

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:

  1. 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
  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.
  3. Create the Server Level DDL trigger "trgMonitorChangeSrv"
  4. 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.

  1. In the SSMS GUI under Server Objects -->Triggers
  2. 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)

  1. In the SSMS GUI under the database name --> Programmability --> Database triggers
  2. 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, that is 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 technical issue "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
Total article views: 7144 | Views in the last 30 days: 12
 
Related Articles
FORUM

How notificate create database event

How notificate create database event

FORUM

How can I use a database event

Database event instead of trigger

FORUM

Error creating a logon trigger

Error creating a logon trigger

FORUM

create Trigger auto.

create Trigger auto.

FORUM

create trigger

create a trigger on SQL Server that will cause an email to be sent to my alias when a database is cr...

Tags
auditing    
sql server 2005    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones