TSQL Tuesday 106 – Trigger Headaches or Happiness: Capturing Database Creations.

I see your hidden database table trigger and raise you a more hidden server one!

Words: 306

Time to read: ~ 1.5 minutes

TSQL Tuesday is around again! This monthly blog party started by Adam Machanic ( blog | twitter ) is still going strong. This month we have Steve Jones ( blog | twitter ) and the topic is all to do with Triggers!

Now, my normal attitude with regard to triggers tends to run to the negative. Which is horrible, because triggers are just like any other tool; neutral by themselves and only good or bad based on how we use them.

So, with that being said, I’ve forced myself to think of a positive use for them. So here is a time when I’ve used triggers for a “good” cause and used them to get some visibility on when new databases are created.

Code:

region Server Trigger
IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = N'NewDatabase') DROP TRIGGER NewDatabase ON ALL SERVER;
GO
CREATE TRIGGER NewDatabase
ON ALL SERVER
FOR CREATE_DATABASE
AS
SELECT EVENTDATA().value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(256)') AS ServerName,
EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(128)') AS DatabaseName,
EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime') AS CreateTime,
EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(256)') AS CreatedBy,
EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)') AS CreationCommand;
GO
endregion
region Simple Database
IF EXISTS (SELECT * FROM sys.databases WHERE [name] = N'NewOne') DROP DATABASE NewOne;
GO
CREATE DATABASE NewOne;
GO
endregion
region Database with options.
IF (SELECT value_in_use FROM sys.configurations WHERE name = N'contained database authentication') <> 1 BEGIN
EXEC sp_configure @configname = N'contained database authentication', @configvalue = 1;
RECONFIGURE
END
IF EXISTS (SELECT * FROM sys.databases WHERE [name] = N'NewYear') DROP DATABASE NewYear;
GO
CREATE DATABASE NewYear
CONTAINMENT = PARTIAL
WITH TWO_DIGIT_YEAR_CUTOFF = 2099;
GO
endregion

The above code is split into 3 regions:

  1. The Server Trigger code,
  2. a Simple Database creation statement, and
  3. a more advanced option where we turn on database containment and change the two_digit_year_cutoff date (basically more code than just CREATE DATABASE)

Want to know why I chose two_digit_year_cutoff? Well, I’ve just read about a nice, little “got’cha” problem with it thanks to Cláudio Silva ( blog | twitter ).

Server Trigger

This Server Trigger captures the

  • Server name,
  • Database name,
  • Creation time,
  • Login name,
  • and the command used to create the database.

When the script is ran as a whole, the results are:

NewDatabases.png
new lines = multiple spaces :/

Simple enough to use sp_send_dbmail to send an email when ever this is run!

Now, when you’re asked to restore a database from backups, you don’t have to go “Hey! Whoa, whoa, whoa! When dahell did that get there?!”

Instead, you can focus on why people feel to need to create databases without you and you can address that while making sure everything is created to your standard.

I’ll take a trigger so that I can have that functionality anyday.

Author: Shane O'Neill

DBA, T-SQL and PowerShell admirer, Food, Coffee, Whiskey (not necessarily in that order)...

3 thoughts on “TSQL Tuesday 106 – Trigger Headaches or Happiness: Capturing Database Creations.”

What's your opinion?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s