Tables Change Auditing

/*
This is used to track database table changes and include the ability to email when tables are altered or dropped. The is accomplished with a DDL trigger. It assumes there is a table in an auditing database to hold the rollback information and a database mail default account.
*/ 


IF  EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'Event_Table_changes')
DISABLE TRIGGER [Event_Table_changes] ON DATABASE

GO

USE [Name of Database]
GO

IF  EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'Event_Table_changes')DROP TRIGGER [Event_Table_changes] ON DATABASE
GO

USE [Manuals]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE trigger [Event_Table_changes]
on database
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
DECLARE @FullFile xml
DECLARE @EventType nvarchar(25)
DECLARE @EventTime datetime
DECLARE @LoginName nvarchar(50)
DECLARE @UserName nvarchar(25)
DECLARE @ObjectName nvarchar(50)
DECLARE @ObjectType nvarchar(25)
DECLARE @Command nvarchar(max)
SET @FullFile = eventdata()
SET @EventType = @FullFile.value('(//EVENT_INSTANCE/EventType)[1]', 'nvarchar(25)')
SET @EventTime = @FullFile.value('(//EVENT_INSTANCE/PostTime)[1]', 'DATETIME')
SET @LoginName = @FullFile.value('(//EVENT_INSTANCE/LoginName)[1]', 'nvarchar(50)')
SET @UserName = @FullFile.value('(//EVENT_INSTANCE/UserName)[1]', 'nvarchar(25)')
SET @ObjectName = @FullFile.value('(//EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(50)')
SET @ObjectType = @FullFile.value('(//EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(25)')
SET @Command = @FullFile.value('(//EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')
--print @command


INSERT INTO [AuditDB].dbo.Table_Events
           ([EventType]
           ,[EventTime]
           ,[LoginName]
           ,[UserName]
           ,[ObjectName]
           ,[ObjectType]
           ,[Command]
           ,[FullFile])
SELECT @EventType
, @EventTime
, @LoginName
, @UserName
, @ObjectName
, @ObjectType
, @Command
, @FullFile

--if table drop email admin group
IF @EventType = 'DROP_TABLE'
BEGIN
     DECLARE @profile varchar(100)
     DECLARE @EmailTo varchar(128)
     DECLARE @Subject varchar(128)
     DECLARE @Body varchar(max)
     DECLARE @BodyType varchar(25)


     SELECT @profile = CAST(COALESCE(SERVERPROPERTY ( 'InstanceName'  ), SERVERPROPERTY ( 'ServerName')) AS varchar(80)) + 'Admin'
     SET @EmailTo = [Add email name]
     SET @Subject = CAST(COALESCE(SERVERPROPERTY ( 'InstanceName'  ), SERVERPROPERTY ( 'ServerName')) AS varchar(80)) +  ' Table has been changed in DB: ' + db_name()
     SET @Body = @Command
     SET @BodyType = 'HTML'

     EXEC msdb.dbo.sp_send_dbmail
     @profile_name = @profile,
     @recipients = @EmailTo ,
     @subject = @Subject,
     @body = @Body ,
     @body_format  = @BodyType
END

GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

DISABLE TRIGGER [Event_Table_changes] ON DATABASE
GO

ENABLE TRIGGER [Event_Table_changes] ON DATABASE
GO

Sql Auditing Changes Stored Procedure Design

/*
I will publish this as I don't have time to finish the documentation but this is the stored procedure that goes with the table below

All users who can drop tables need to be able to send and email or EXECUTE AS need to be applied to allow the stored procedure to both send the email and write the record.
*/

USE [Common]
GO
/****** Object: DdlTrigger [Event_SP_changes] Script Date: 06/02/2009 16:58:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create trigger [Event_SP_changes]
on database --WITH EXECUTE AS 'UserName'
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
DECLARE @FullFile xml
DECLARE @EventType nvarchar(25)
DECLARE @EventTime datetime
DECLARE @LoginName nvarchar(50)
DECLARE @UserName nvarchar(25)
DECLARE @ObjectName nvarchar(50)
DECLARE @ObjectType nvarchar(25)
DECLARE @Command nvarchar(max)
SET @FullFile = eventdata()
SET @EventType = @FullFile.value('(//EVENT_INSTANCE/EventType)[1]', 'nvarchar(25)')
SET @EventTime = @FullFile.value('(//EVENT_INSTANCE/PostTime)[1]', 'DATETIME')
SET @LoginName = @FullFile.value('(//EVENT_INSTANCE/LoginName)[1]', 'nvarchar(50)')
SET @UserName = @FullFile.value('(//EVENT_INSTANCE/UserName)[1]', 'nvarchar(25)')
SET @ObjectName = @FullFile.value('(//EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(50)')
SET @ObjectType = @FullFile.value('(//EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(25)')
SET @Command = @FullFile.value('(//EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')
--print @command
INSERT INTO [AuditDB].dbo.Common_SP_Events
([EventType]
,[EventTime]
,[LoginName]
,[UserName]
,[ObjectName]
,[ObjectType]
,[Command]
,[FullFile])
SELECT @EventType
, @EventTime
, @LoginName
, @UserName
, @ObjectName
, @ObjectType
, @Command
, @FullFile



GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [Event_SP_changes] ON DATABASE



----------------
Now playing: NPR - 0905150: NPR: 05-30-2009 Wait Wait... Don't Tell Me!
via FoxyTunes

Sql Auditing Changes Table Design

/*
This is the start of documenting the process by which I collect information about changes in the database. This is done through ddl triggers and I keep a copy of all of the changes. This step is creating an audit table in the AuditDB. I always give myself one database on every server to collect this kind of information. The later posts will have the stored procedure to populate this table
*/

USE [AuditDB]
GO
/****** Object: Table [dbo].[Common_SP_Events] Script Date: 06/02/2009 16:48:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Common_SP_Events](
[EventType] [nvarchar](25) NULL,
[EventTime] [datetime] NULL CONSTRAINT [DF_Common_SP_Events_EventTime] DEFAULT (getdate()),
[LoginName] [nvarchar](50) NULL,
[UserName] [nvarchar](25) NULL,
[ObjectName] [nvarchar](50) NULL,
[ObjectType] [nvarchar](25) NULL,
[Command] [nvarchar](max) NULL,
[FullFile] [xml] NULL
) ON [PRIMARY];

CREATE CLUSTERED INDEX [cdx_Common_SP_Events_EventTime] ON [dbo].[Common_SP_Events]
(
[EventTime] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF
, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];