DDL Trigger on Database Drop

Standard

I recently got a problem that somebody from the organisation was deleting databases. Because a few users had the local administrator right rights they had the ability to drop tables. Because SQL Server does not log who dropped the database I had no choice than to log this with a ddl trigger.

The table is going to be created in a database called “DBA”. You can save the data in any table/database you want but be sure that this is also changed in the code of the trigger.

I wanted to log these actions in a table. This is SQL code for the table:

CREATE TABLE [dbo].[ddl_EventLog](
[ID] [int] IDENTITY(1,1) NOT NULL,
[EventTime] [datetime] NULL,
[EventType] [varchar](15) NULL,
[ServerName] [varchar](25) NULL,
[DatabaseName] [varchar](25) NULL,
[ObjectType] [varchar](25) NULL,
[ObjectName] [varchar](25) NULL,
[UserName] [varchar](15) NULL,
[CommandText] [varchar](max) NULL
) ON [PRIMARY]

The DDL trigger code:

CREATE TRIGGER [trg_dropdatabase_log]
ON ALL SERVER -- Create Server DDL Trigger
FOR DROP_DATABASE -- Trigger will raise when dropping a database
AS
SET NOCOUNT ON

DECLARE @xmlEventData XML

-- Capture the event data that is created
SET @xmlEventData = eventdata()

-- Insert information to a EventLog table.
-- Make sure the database and table exists and that the script points to the right database and table.
INSERT INTO [DBA].dbo.ddl_EventLog
(
EventTime,
EventType,
ServerName,
DatabaseName,
ObjectType,
ObjectName,
UserName,
CommandText
)
SELECT REPLACE(CONVERT(VARCHAR(50), @xmlEventData.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),
CONVERT(VARCHAR(15), @xmlEventData.query('data(/EVENT_INSTANCE/EventType)')),
CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ServerName)')),
CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/DatabaseName)')),
CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectType)')),
CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectName)')),
CONVERT(VARCHAR(15), @xmlEventData.query('data(/EVENT_INSTANCE/UserName)')),
CONVERT(VARCHAR(MAX), @xmlEventData.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))

GO

If you want to edit or remove the trigger you can find it in the “Server Objects”, “Triggers” in SQL Server Management Studio.

Hope you enjoy the script. Any comments are appreciated.

Leave a Reply

Your email address will not be published.