SQL Server 2005 DDL Event Logging

<< Inserting Code Snippets in Community Server | Home | Geek Humor >>
SQL Server 2005 DDL Event Logging

Wouldn’t it be nice to have a record of every DDL (structural) change to your SQL Server? Well you can do it very easily in SQL Server 2005. Microsoft has even provided you the script to do it, aren’t they nice. (How about just creating a check box to turn it on in the next SQL Server?) I have included the script below for those of you that have not installed the SQL Server 2005 Samples. When I used it I modified the script below two ways. First I set it up only to log to a relational table (I did not want the XML log) and I set it to log all DDL events on the entire server. Take a look at the following knowledge base article to see a tree of the events that you can point at. (http://msdn2.microsoft.com/en-us/library/ms191441.aspx). Finally this does require the use of the service broker and the users will need some permissions in msdb, depending on how your DBA setup the server. One more idea for you to ponder, if we are able to log when an event happens, what other actions can we take (email someone, set permissions on the item, update statistics, etc.).
--------------------------------------------------------------------
-- Summary: Script for EventLogging sample.
--
-- This file is part of the Microsoft SQL Server Code Samples.
-- Copyright (C) Microsoft Corporation. All Rights reserved.
-- This source code is intended only as a supplement to Microsoft
-- Development Tools and/or on-line documentation. See these other
-- materials for detailed information regarding Microsoft code samples.
--
-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO
-- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
-- PARTICULAR PURPOSE.
--------------------------------------------------------------------
-- This script sets up a simple event logging sample in AdventureWorks.
-- The sample receives event notifications and logs information from the
-- notifications into two tables.
--------------------------------------------------------------------
-- Activate Service Broker message delivery in AdventureWorks.
-- Activate Service Broker message delivery in AdventureWorks. A Beta 2
-- limitation of the setup procedure deactivates message delivery during installation.
use master ;
GO
-- If Service Broker message delivery is not active in AdventureWorks, alter the
-- database to make message delivery active.
IF NOT EXISTS (SELECT * FROM sys.databases
               WHERE name = 'AdventureWorks'
               AND is_broker_enabled = 1)
BEGIN
    ALTER DATABASE AdventureWorks SET ENABLE_BROKER ;
END ;
GO
--------------------------------------------------------------------
--------------------------------------------------------------------
-- Create the EventLogging service.
-- This section creates a queue for the service to use and then
-- creates the service. Services that receive event notification
-- messages implement the contract
-- http://schemas.microsoft.com/SQL/Notifications/PostEventNotification .
use AdventureWorks ;
GO
-- If necessary, drop the service and then the queue.
IF EXISTS(SELECT * FROM sys.services WHERE name = 'NotifyService')
    DROP SERVICE NotifyService ;
GO
IF OBJECT_ID('[dbo].[NotifyQueue]') IS NOT NULL AND
   EXISTS(SELECT * FROM sys.service_queues WHERE name = 'NotifyQueue')
  DROP QUEUE [dbo].[NotifyQueue];
GO
-- Create the queue for the service. For the time being, do not specify activation.
CREATE QUEUE NotifyQueue;
GO
-- Create a Service to receive Event Notifications. The service
-- accepts conversations on the system event notification contract.
CREATE SERVICE NotifyService
ON QUEUE NotifyQueue
(
[http://schemas.microsoft.com/SQL/Notifications/PostEventNotification] 
);
GO
--------------------------------------------------------------------
--------------------------------------------------------------------
-- Create tables to store event information.
-- This part of the script creates two tables to store the contents of 
-- event notification messages. The first table, LoggedEvents, stores the
-- information as relational data. The second table, LoggedEventsXML, stores
-- the event type and the time of the event as relational data, while storing
-- the event message itself as XML. This script includes both approaches
-- for demonstration purposes. A production application would likely choose
-- one or the other of the two strategies.
-- The LoggedEvents table holds event notification information in relational format.
IF OBJECT_ID('[dbo].[LoggedEvents]') IS NOT NULL
   AND OBJECTPROPERTY(OBJECT_ID('[dbo].[LoggedEvents]'), 'IsTable') = 1
  DROP TABLE [dbo].[LoggedEvents] 
GO
CREATE TABLE [dbo].[LoggedEvents] (
    EventNumber INT IDENTITY PRIMARY KEY,
    EventType   NVARCHAR(256),
    EventTime   DATETIME,
    LoginName   sysname NULL,
    UserName    sysname NULL,
    ServerName  sysname NULL,
    DatabaseName sysname NULL,
    SchemaName  sysname NULL,
    ObjectName  sysname NULL,
    ObjectType  sysname NULL,
    TSQLCmdText NVARCHAR(MAX) NULL
    ) ;
GO
-- The LoggedEventsXML table shows another way of logging event data.
-- In this case, the event type and time are extracted from the event
-- message. The entire message is saved in the table.
IF OBJECT_ID('[dbo].[LoggedEventsXML]') IS NOT NULL
   AND OBJECTPROPERTY(OBJECT_ID('[dbo].[LoggedEventsXML]'), 'IsTable') = 1
  DROP TABLE [dbo].[LoggedEventsXML] 
GO
CREATE TABLE [LoggedEventsXML] (
    EventNumber INT IDENTITY PRIMARY KEY,
    EventType   NVARCHAR(256),
    EventTime   DATETIME,
    EventData   XML
    ) ;
GO
--------------------------------------------------------------------
--------------------------------------------------------------------
-- Create the stored procedure for processing event messages.
-- This stored procedure is the service program for the event logging application.
-- The procedure reads each event notification message from the queue and 
-- logs the information from the message into both tables.
IF  OBJECT_ID(N'[dbo].[LogEventsProc]') IS NOT NULL AND
    OBJECTPROPERTY(OBJECT_ID(N'[dbo].[LogEventsProc]'), N'IsProcedure') = 1
    DROP PROCEDURE [dbo].[LogEventsProc] ;
GO
-- Set options required for the XML data type.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[LogEventsProc]
AS
SET NOCOUNT ON;
DECLARE     @message_body XML,
            @message_type_name NVARCHAR(256),
            @dialog UNIQUEIDENTIFIER ;
--  This procedure continues to process messages in the queue until the
--  queue is empty.
WHILE (1 = 1)
BEGIN
    BEGIN TRANSACTION ;
    -- Receive the next available message
    WAITFOR (
        RECEIVE TOP(1) -- just handle one message at a time
            @message_type_name=message_type_name,  --the type of message received
            @message_body=message_body,      -- the message contents
            @dialog = conversation_handle    -- the identifier of the dialog this message was received on
            FROM NotifyQueue
    ), TIMEOUT 2000 ; -- if the queue is empty for two seconds, give up and go away
   -- If RECEIVE did not return a message, roll back the transaction
   -- and break out of the while loop, exiting the procedure.
    IF (@@ROWCOUNT = 0)
        BEGIN
            ROLLBACK TRANSACTION ;
            BREAK ;
        END ;
   -- Check to see if the message is an end dialog message.
    IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
    BEGIN
        PRINT 'End Dialog received for dialog # ' + cast(@dialog as nvarchar(40)) ;
        END CONVERSATION @dialog ;
    END ;
    ELSE
    BEGIN
    -- Extract the event information using XQuery.
    
    -- Use XQuery to extract XML values to be inserted into the log table
    INSERT INTO [dbo].[LoggedEvents] (
        EventType,
        EventTime,
        LoginName,
        UserName,
        ServerName,
        DatabaseName,
        SchemaName,
        ObjectName,
        ObjectType,
        TSQLCmdText
    )
    VALUES
    (
    CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()')  AS NVARCHAR(256)),
    CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS NVARCHAR(MAX)) AS DATETIME),
    CAST(@message_body.query('/EVENT_INSTANCE/LoginName/text()') AS sysname),
    CAST(@message_body.query('/EVENT_INSTANCE/UserName/text()') AS sysname),
    CAST(@message_body.query('/EVENT_INSTANCE/ServerName/text()') AS sysname),
    CAST(@message_body.query('/EVENT_INSTANCE/DatabaseName/text()') AS sysname),
    CAST(@message_body.query('/EVENT_INSTANCE/SchemaName/text()') AS sysname),
    CAST(@message_body.query('/EVENT_INSTANCE/ObjectName/text()') AS sysname),
    CAST(@message_body.query('/EVENT_INSTANCE/ObjectType/text()') AS sysname),
    CAST(@message_body.query('/EVENT_INSTANCE/TSQLCommand/CommandText/text()') AS NVARCHAR(MAX))
    ) ;
    
    -- Insert the message body as XML into the loggedeventsXML table
    INSERT INTO [dbo].[LoggedEventsXML] (
        EventType,
        EventTime,
        EventData
    )
    VALUES
    (
    CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()')  AS NVARCHAR(256)),
    CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS NVARCHAR(MAX)) AS datetime),
    @message_body
    ) ;
    END ;
    COMMIT TRANSACTION ;
END ;
GO
--------------------------------------------------------------------
--------------------------------------------------------------------
-- Configure the queue for activation.
-- Alter the queue to add the SP we just created as an activated stored procedure
-- This will cause this procedure to run automatically when events are being received.
ALTER QUEUE [dbo].[NotifyQueue]
    WITH ACTIVATION (
        -- Activation turned on
        STATUS = ON,
        -- The name of the proc to process messages for this queue                              
        PROCEDURE_NAME = [dbo].[LogEventsProc] ,
        -- The maximum number of copies of the proc to start 
        MAX_QUEUE_READERS = 2,
        -- Start the procedure as the user who created the queue.                     
        EXECUTE AS SELF                            
    ) 
;
--------------------------------------------------------------------
--------------------------------------------------------------------
-- Summary: Demonstrates EventLogging sample.
-- 
--------------------------------------------------------------------
-- This file is part of the Microsoft SQL Server Code Samples.
-- Copyright (C) Microsoft Corporation. All Rights reserved.
-- This source code is intended only as a supplement to Microsoft
-- Development Tools and/or on-line documentation. See these other
-- materials for detailed information regarding Microsoft code samples.
--
-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO
-- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
-- PARTICULAR PURPOSE.
--------------------------------------------------------------------
-- This script registers event notifications and creates events.
USE AdventureWorks ;
GO
--------------------------------------------------------------------
-- Register a notification for the CREATE_DATABASE server event.
--------------------------------------------------------------------
-- Drop the notification if it already exists.
IF EXISTS(SELECT * FROM sys.server_event_notifications
          WHERE name = 'CreateDatabaseNotification')
  DROP EVENT NOTIFICATION CreateDatabaseNotification ON SERVER ;
GO
-- Set up the notification.
CREATE EVENT NOTIFICATION CreateDatabaseNotification
-- This notification is scoped to the server.
ON SERVER
-- This notification is for Create Database events.                   
FOR CREATE_DATABASE
-- Send messages for this notification to the NotifyService.         
TO SERVICE 'NotifyService', 'current database';
GO
--------------------------------------------------------------------
--------------------------------------------------------------------
-- Generate CREATE_DATABASE events.
--------------------------------------------------------------------
-- Drop the database if it already exists.
IF DB_ID('TestNotification') IS NOT NULL
    DROP DATABASE TestNotification;
GO
CREATE DATABASE TestNotification ;
GO
DROP DATABASE TestNotification ;
GO
CREATE Database TestNotification ;
GO
--------------------------------------------------------------------
--------------------------------------------------------------------
-- Register a notification for the CREATE_TABLE database event
-- in the TestNotification database.
--------------------------------------------------------------------
USE TestNotification
GO
-- Set up the notification.
CREATE EVENT NOTIFICATION CreateTableNotification
-- This notification is scoped to the current database
ON DATABASE
-- We want to be notified of Create Table events   
FOR CREATE_TABLE
-- When a database is created, send a message to the NotifyService         
TO SERVICE 'NotifyService', 'current database';
GO
--------------------------------------------------------------------
--------------------------------------------------------------------
-- Generate a CREATE_TABLE message.
--------------------------------------------------------------------
USE TestNotification
GO
Create Table [TestTable1] (Column1 int, Column2 int)
GO
USE TestNotification
GO
--------------------------------------------------------------------
--------------------------------------------------------------------
-- Display the results.
--------------------------------------------------------------------
use AdventureWorks
GO
-- Wait to allow message delivery.
waitfor DELAY '0:00:1'
-- Select from the table that stores events as relational data.
select * from [dbo].[LoggedEvents]
-- Select from the table that stores the original XML message.
select * from [dbo].[LoggedEventsXML]
GO
--------------------------------------------------------------------
--------------------------------------------------------------------
-- Summary: Removes objects created by EventLogging sample.
-- 
--------------------------------------------------------------------
-- This file is part of the Microsoft SQL Server Code Samples.
-- Copyright (C) Microsoft Corporation. All Rights reserved.
-- This source code is intended only as a supplement to Microsoft
-- Development Tools and/or on-line documentation. See these other
-- materials for detailed information regarding Microsoft code samples.
--
-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO
-- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
-- PARTICULAR PURPOSE.
--------------------------------------------------------------------
use master ;
GO
-- Drop the database created to show CREATE_DATABASE events, if necessary.
IF DB_ID('TestNotification') IS NOT NULL
    DROP DATABASE TestNotification;
GO
use AdventureWorks ;
GO
-- Drop the server event notification.
IF EXISTS(SELECT * FROM sys.server_event_notifications
          WHERE name = 'CreateDatabaseNotification')
  DROP EVENT NOTIFICATION CreateDatabaseNotification ON SERVER ;
GO
-- Drop the database event notification.
IF EXISTS(SELECT * FROM sys.event_notifications
          WHERE name = 'CreateTableNotification')
  DROP EVENT NOTIFICATION CreateTableNotification ON DATABASE ;
GO
-- Drop the service.
IF EXISTS(SELECT * FROM sys.services WHERE name = 'NotifyService')
    DROP SERVICE NotifyService ;
GO
-- Drop the queue.
IF OBJECT_ID('[dbo].[NotifyQueue]') IS NOT NULL AND
   EXISTS(SELECT * FROM sys.service_queues WHERE name = 'NotifyQueue')
  DROP QUEUE [dbo].[NotifyQueue];
GO
-- Drop the LoggedEvents table.
IF OBJECT_ID('[dbo].[LoggedEvents]') IS NOT NULL
   AND OBJECTPROPERTY(OBJECT_ID('[dbo].[LoggedEvents]'), 'IsTable') = 1
  DROP TABLE [dbo].[LoggedEvents] 
GO
-- Drop the LoggedEventsXML table.
IF OBJECT_ID('[dbo].[LoggedEventsXML]') IS NOT NULL
   AND OBJECTPROPERTY(OBJECT_ID('[dbo].[LoggedEventsXML]'), 'IsTable') = 1
  DROP TABLE [dbo].[LoggedEventsXML] 
GO
Print | posted on Monday, November 06, 2006 7:52 PM | Filed Under [ SQL ]
Feedback

No comments posted yet.
Post Comment

Title
Name
Email
Url
Comment
Remember Me?
Please add 8 and 3 and type the answer here: Enter the code shown above:
Shawn Weisfeld

My Links

News

Archives

Post Categories

SQL Server 2005 DDL Event Logging

Feedback

Post Comment
