How to create a DDL Trigger to notify on New Database Creation

Using a DDL Trigger to Monitor New Database Creation in SQL Server

Unplanned databases are one of those problems that sneak up on environments quietly. A developer spins up a test database, a SharePoint farm auto-provisions a content database, or someone runs a CREATE DATABASE statement without telling anyone. Before long, your backup coverage has gaps, your storage is unaccounted for, and your change management process has a hole in it.

A DDL trigger on the SQL Server instance solves this directly. You can create a server-scoped DDL trigger that fires automatically whenever a new database is created, sending an email notification to your DBA team or support group in real time. This works in SQL Server 2008 and all later versions.

What Is a DDL Trigger and Why Use One Here?

SQL Server supports two broad categories of triggers: DML triggers, which fire on data changes like INSERT, UPDATE, and DELETE, and DDL triggers, which fire on structural changes like CREATE, ALTER, and DROP statements.

A DDL trigger scoped to ALL SERVER fires at the instance level, not just within a single database. That makes it the right tool for monitoring events that affect the server itself, including database creation. When a CREATE DATABASE statement executes, the trigger captures the event data, builds a notification email, and sends it via Database Mail before the operation completes.

This is a lightweight, low-overhead approach. The trigger adds negligible latency to the CREATE DATABASE operation, and once it's in place you don't need to rely on anyone remembering to notify the team.

What You Need Before Deploying This Trigger

Before creating the DDL trigger, you need Database Mail configured and a mail profile set up on the instance. If you haven't done this yet, the trigger will fail silently at the sp_send_dbmail step.

Check that Database Mail is working with a quick test:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SQL Monitor',
    @recipients = 'supportGroup@domain.com',
    @subject = 'Database Mail Test',
    @body = 'If you received this, Database Mail is working.';

If that delivers successfully, you're ready to proceed. If not, resolve the mail configuration first. Microsoft's documentation on configuring Database Mail is the reference point here.

You'll also need sysadmin or appropriate server-level permissions to create a server-scoped DDL trigger.

The DDL Trigger Script

The following script creates a DDL trigger that fires on every CREATE DATABASE event at the instance level. It captures the T-SQL command that was executed, the login name, server name, hostname, and timestamp, then sends an HTML-formatted email to your nominated recipients.

Update the @profile_name and @recipients values to match your environment before running this.

CREATE TRIGGER [DDL_CREATE_DATABASE_EVENT]
ON ALL SERVER
FOR CREATE_DATABASE
AS
DECLARE @bd varchar(max)
DECLARE @tsql varchar(max)
DECLARE @sub varchar(max)
DECLARE @newDB varchar(max)
DECLARE @fint int

SET @tsql = EVENTDATA().value
    ('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'varchar(max)')

SET @fint = CHARINDEX('[', @tsql) - 1
SET @newDB = SUBSTRING(@tsql, CHARINDEX('[', @tsql), CHARINDEX(']', @tsql) - @fint)

SET @bd = '<b>PLEASE REMEMBER TO DO A FULL BACKUP OF ' + @newDB + '!!</b><br><br>
UserName: ' + UPPER(SUSER_NAME()) + '<br>
ServerName: ' + @@SERVERNAME + '<br>
Time: ' + CONVERT(varchar(25), GETDATE()) + '<br>
HostName: ' + HOST_NAME() + '<br>
Database: <b>' + @newDB + '</b><br><br>
T-SQL: ' + @tsql + '<br><br><br>'

SET @sub = 'A new database ' + @newDB + ' has been created on ' + @@SERVERNAME

BEGIN
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'SQL Monitor',
        @recipients = 'supportGroup@domain.com',
        @subject = @sub,
        @body_format = 'HTML',
        @importance = 'High',
        @body = @bd
END
GO

ENABLE TRIGGER [DDL_CREATE_DATABASE_EVENT] ON ALL SERVER
GO

What the Trigger Actually Does

It's worth stepping through the logic so you understand what you're deploying.

The EVENTDATA() function is the key piece. It returns an XML fragment containing details about the DDL event that fired the trigger. The XQuery expression extracts the raw T-SQL command text from that XML, giving you exactly what was executed.

The CHARINDEX and SUBSTRING logic then parses the database name out of that command text by looking for the bracket-delimited identifier. This is a reasonable approach for standard CREATE DATABASE statements, though it assumes the database name is enclosed in square brackets. If your environment uses unquoted database names in the CREATE DATABASE syntax, you may need to adjust this parsing logic.

The email body is assembled as HTML, which is why the @body_format is set to 'HTML'. The notification includes the database name, the login that ran the command, the server name, the hostname of the client connection, the timestamp, and the full T-SQL that was executed. That last detail is particularly useful for auditing purposes because it captures any non-default options that were specified.

The email is flagged as high importance, which helps it stand out in a busy support inbox.

Verifying the Trigger Is Active

After running the script, confirm the trigger exists and is enabled:

SELECT
    name,
    type_desc,
    is_disabled,
    create_date,
    modify_date
FROM sys.server_triggers
WHERE name = 'DDL_CREATE_DATABASE_EVENT';

You should see is_disabled = 0. If you need to temporarily disable the trigger, for example during a migration where you're creating many databases in bulk, use:

DISABLE TRIGGER [DDL_CREATE_DATABASE_EVENT] ON ALL SERVER;

Re-enable it once you're done:

ENABLE TRIGGER [DDL_CREATE_DATABASE_EVENT] ON ALL SERVER;

The SharePoint and Application Service Account Issue

This is the catch that trips people up most often. Some applications create databases automatically using their own service accounts. SharePoint is the most common example, but the same applies to certain monitoring tools, third-party applications, and automated provisioning scripts.

When a service account executes a CREATE DATABASE statement, the DDL trigger fires under that account's context. If the service account doesn't have EXECUTE permission on the trigger, or more precisely, doesn't have the necessary permissions to execute sp_send_dbmail, the trigger will fail and an error will be raised.

The practical fix is to ensure the service account has EXECUTE permission on the msdb database mail stored procedure, or to wrap the email send in a TRY/CATCH block so a permissions failure doesn't block the database creation itself.

Here's a safer version of the trigger body that won't block database creation if the mail send fails:

BEGIN TRY
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'SQL Monitor',
        @recipients = 'supportGroup@domain.com',
        @subject = @sub,
        @body_format = 'HTML',
        @importance = 'High',
        @body = @bd
END TRY
BEGIN CATCH
    -- Log the failure or handle silently
    -- The database creation will still proceed
END CATCH

This is the version we'd recommend for production environments. You don't want a mail configuration issue to block legitimate database creation.

Key Takeaways

• A server-scoped DDL trigger using FOR CREATE_DATABASE fires automatically whenever any database is created on the instance, regardless of who creates it or how.
• The EVENTDATA() function gives you full context about the event including the exact T-SQL executed, the login name, and the timestamp.
• Database Mail must be configured and working before deploying this trigger. Test it independently first.
• Applications like SharePoint that auto-create databases using service accounts can cause the trigger to fail if permissions aren't set correctly. Use TRY/CATCH to prevent mail failures from blocking database creation.
• Disable the trigger temporarily if you're running bulk database provisioning operations, then re-enable it immediately afterwards.

If you're looking to formalise change management controls across your SQL Server environment, this kind of DDL trigger is one of many practical safeguards that DBA Services implements as part of our managed support and SQL Server health check engagements. Getting visibility over what's changing on your instances, and when, is foundational to running a well-governed database environment. Talk to the DBA Services team about what that looks like for your organisation.