the below script with steps included will create databses , tables and triggers under server and test databse
the AuditNewDatabaseCreation1 will emails and logs details in table whene ever a databse is created or dropped and Trigger:AuditObjects1 will be created in test database and when ever a object is created , altered, dropped , it lohs information and emails
-----Step1: Create Database: AuditDatabase
-----Step2:Create table as below
USE [AuditDatabase]
CREATE TABLE [dbo].[DatabaseCreateDropTracking](
[TrackingId] [int] IDENTITY(1,1) NOT NULL,
[TrackingDate] [datetime] NOT NULL,
[DatabaseName] [varchar](100) NULL,
[EventType] [varchar](100) NULL,
[LoginName] [varchar](100) NULL,
[InstanceName] [varchar](100) NULL,
[SQLCommand] [nvarchar](max) NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[DatabaseCreateDropTracking] ADD CONSTRAINT [DF_DatabaseCreateDropTracking_TrackingDate] DEFAULT (getdate()) FOR [TrackingDate]
GO
-----Step3:create table
USE [AuditDatabase]
--CREATE TABLE [dbo].[ObjectTracking](
[TrackingId] [bigint] IDENTITY(1,1) NOT NULL,
[TrackingDate] [datetime] NOT NULL,
[DatabaseName] [varchar](500) NULL,
[EventType] [varchar](500) NULL,
[ObjectName] [varchar](500) NULL,
[ObjectType] [varchar](500) NULL,
[LoginName] [varchar](500) NULL,
[HostName] [varchar](500) NULL,
[SqlCommand] [nvarchar](max) NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[ObjectTracking] ADD DEFAULT (getdate()) FOR [TrackingDate]
GO
------Step4:THIS below TRIGGERS EMAILS when a database is created or dropped and keep track in tables in AuditDatabase database
/****** Object: DdlTrigger [AuditNewDatabaseCreation1] Script Date: 03/14/2013 09:01:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [AuditNewDatabaseCreation1]
ON ALL SERVER
FOR
-- DDL_DATABASE_LEVEL_EVENTS
-- Defining all the DDL database events on which we need track
CREATE_DATABASE, DROP_DATABASE
AS
BEGIN
--DECLARE @dbname varchar(500)
DECLARE @data XML
DECLARE @InstanceName varchar(100)
Declare @tsql varchar(max)
DECLARE @bd varchar(max)
--SET @dbname = DB_NAME()
SET @data = EVENTDATA()
SET @InstanceName = HOST_NAME()
INSERT INTO AuditDatabase.dbo.DatabaseCreateDropTracking(DatabaseName,EventType,LoginName,InstanceName,SqlCommand)
VALUES(
--@dbname,
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(100)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(100)'),
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(100)'),
@InstanceName,
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)')
)
Set @tsql = EVENTDATA().value
('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','varchar(max)')
SET @bd = 'UserName: ' + UPPER(SUSER_NAME()) + '
ServerName: ' + @@SERVERNAME + '
Time: ' + CONVERT(varchar(25),Getdate()) + '
HostName: ' + HOST_NAME() + '
Database: ' + db_name() + '
T-SQL: ' + @tsql
IF @bd LIKE '%drop%'
--;astratto@chesapeakebay.net
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Database Mail ',
@recipients = 'xyz@abc.com',
@subject = 'A new database has been dropped ',
@body_format = 'HTML',
@importance = 'High',
@body = @bd
ELSE
--;astratto@chesapeakebay.net
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Database Mail',
@recipients = 'xyz@abc.com',
@subject = 'A new database has been created ',
@body_format = 'HTML',
@importance = 'High',
@body = @bd
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [AuditNewDatabaseCreation1] ON ALL SERVER
GO
----STEP6:
CREATE DATABASE TEST
GO
USE [test]
GO
CREATE TRIGGER [AuditObjects1]
ON DATABASE
FOR
-- DDL_DATABASE_LEVEL_EVENTS
-- Defining all the DDL database events on which we need track
CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
CREATE_VIEW,ALTER_VIEW,DROP_VIEW,
CREATE_INDEX,ALTER_INDEX,DROP_INDEX
AS
BEGIN
DECLARE @data XML
DECLARE @HostName varchar(500)
SET @data = EVENTDATA()
SET @HostName = HOST_NAME()
Declare @tsql varchar(max)
DECLARE @bd varchar(max)
INSERT INTO AuditDatabase.dbo.ObjectTracking(DatabaseName,EventType,ObjectName,ObjectType,LoginName,HostName,SqlCommand)
VALUES(
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(500)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(500)'),
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(500)'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(500)'),
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(500)'),
@HostName,
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)')
)
Set @tsql = EVENTDATA().value
('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','varchar(max)')
SET @bd = 'UserName: ' + UPPER(SUSER_NAME()) + '
ServerName: ' + @@SERVERNAME + '
Time: ' + CONVERT(varchar(25),Getdate()) + '
HostName: ' + HOST_NAME() + '
Database: ' + db_name() + '
T-SQL: ' + @tsql
--PRINT 'Database has been created or dropped '
IF @bd LIKE '%CREATE%'
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'CBPO Database Mail Admins',
@recipients = 'xyz@abc.com',
@subject = 'An Object is created',
@body_format = 'HTML',
@importance = 'High',
@body = @bd
IF @bd LIKE '%ALTER%'
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Database Mail',
@recipients = 'xyz@abc.com',
@subject = 'An Object is altered ',
@body_format = 'HTML',
@importance = 'High',
@body = @bd
IF @bd LIKE '%DROP%'
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Database Mail',
@recipients = 'xyz@abc.com',
@subject = 'An Object is Dropped ',
@body_format = 'HTML',
@importance = 'High',
@body = @bd
END
GO
ENABLE TRIGGER [AuditObjects1] ON DATABASE
GO
