Enforce SQL Server Database Naming Conventions Using DDL Triggers

By: Aaron Bertrand | Comments | Related: More > Database Administration

Problem

In a previous tip, I showed how to enforce a database naming convention for existing databases using Policy-Based Management. In this tip, I want to show how to enforce a naming convention for databases using a simple DDL trigger.

Solution

For creating a new database, we can capture this activity using the CREATE_DATABASE event in a DDL trigger. A very simple example is as follows:

USE [master];
GO
CREATE TRIGGER PreventBadDatabaseNames
ON ALL SERVER
FOR CREATE_DATABASE
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @name NVARCHAR(255);
  SET @name = LOWER(EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(255)'));
  IF @name = N'foo' 
    -- or @name LIKE N'%somepattern%'
    -- or @name IN (SELECT name FROM dbo.BadNameLibrary) 
  BEGIN
    RAISERROR('You used a bad name [%s].', 11, 1, @name);
    ROLLBACK TRANSACTION;
  END
END
GO

Here I am just trapping for a specific "bad" name (foo), but show how you could extend the rules to trap various pattern matches, or even use a table of known words. The LOWER() is used to protect ourselves from case sensitive collations (just make sure that both sides of the clause use lower case).

Now, if I try to create a database named foo, I will get this error:

Msg 50000, Level 11, State 1, Procedure PreventBadDatabaseNames
You used a bad name [foo].
Msg 3609, Level 16, State 2
The transaction ended in the trigger. The batch has been aborted.

That takes care of the simple case - a user manually tries to create a database called foo, and we stop them in their tracks. Now if only life were that simple...

Challenges

Of course, there are other ways to get a database named foo without manually creating it. I can attach a database with that name, or I can change the name of a database (e.g. using sp_renamedb, which is deprecated) that has already passed the tests in my DDL trigger.

Attaching a database

In this case, the DDL trigger fires for the CREATE_DATABASE event, whether you use CREATE DATABASE ... FOR ATTACH[_REBUILD_LOG] or sp_attach_db (also deprecated). The problem is that, even though the trigger attempts to perform a rollback, the database remains created. You will see the same pair of error messages as above, but you will also notice that the database did, in fact, get created.

So we need to add a little logic that checks if the database exists, and detaches it again. We likely don't want to drop the new database, but rather have them try again with a name we want to accept. We can detach it using sp_detach_db (not deprecated, by the way):

ALTER TRIGGER PreventBadDatabaseNames
ON ALL SERVER
FOR CREATE_DATABASE
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @name NVARCHAR(255);
  SET LOWER(@name = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(255)'));
  IF @name = N'foo' 
    -- or @name LIKE N'%somepattern%'
    -- or @name IN (SELECT name FROM dbo.BadNameLibrary) 
  BEGIN
    RAISERROR('You used a bad name [%s].', 11, 1, @name);
    ROLLBACK TRANSACTION;
    IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @name)
    BEGIN
      EXEC sp_detach_db @dbname = @name;
    END
  END
END
GO

Now we can prevent bad database names from being attached as well.

Renaming a database

When you rename a database (using sp_renamedb or ALTER DATABASE good_name MODIFY NAME = bad_name;), the event that a DDL trigger will capture is ALTER_DATABASE. Unlike with NewObjectName for the RENAME event on a database-level DDL trigger, there is no RENAME_DATABASE event, nor is there a NewDatabaseName attribute in the EventData() XML. This is what the entire XML looks like for a rename:

<EVENT_INSTANCE>
  <EventType>ALTER_DATABASE</EventType>
  <PostTime>2014-01-01T00:00:00.000</PostTime>
  <SPID>51</SPID>
  <ServerName>BEAKER</ServerName>
  <LoginName>BEAKER\Aaron</LoginName>
  <DatabaseName>old_name</DatabaseName>
  <TSQLCommand>
    <SetOptions ... />
    <CommandText>ALTER DATABASE [old_name] MODIFY NAME = [new_name]</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

You'll notice that the only place where new_name appears is in the CommandText property. This means that in order to check if the new name violates your convention, you need to parse and extract the name from the entire command. Adding to the challenge, the format of the command is quite predictable when using sp_renamedb, but not so much when using ALTER DATABASE. On the plus side, it doesn't appear possible to combine MODIFY NAME with other ALTER DATABASE options.

Here is a modified trigger that handles both CREATE_DATABASE and ALTER_DATABASE events. If the database is a new database, the transaction is rolled back, and a detach is attempted in the event that the rollback didn't actually prevent the creation. If the database is an existing database, we check to see that it is a rename event, and if the new name doesn't meet our naming standards, we name it back to the old name.

ALTER TRIGGER PreventBadDatabaseNames
ON ALL SERVER
FOR CREATE_DATABASE, ALTER_DATABASE
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE 
    @name    NVARCHAR(255), 
    @event   NVARCHAR(255), 
    @cmd     NVARCHAR(MAX), 
    @oldname NVARCHAR(255),
    @act     BIT = 0;
  SELECT 
    @name = LOWER(EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(255)')),
    @event = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','NVARCHAR(255)'),
    @cmd = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','NVARCHAR(MAX)');
  IF @event = N'ALTER_DATABASE' AND LOWER(@cmd) LIKE N'%modify%name%=%'
  BEGIN
    SELECT @oldname = @name,
      @name = LOWER(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(SUBSTRING(@cmd,
        CHARINDEX(N'=', @cmd) + 1, 255))),';',''),'[',''),']',''));
  END
  IF @name = N'foo' 
    -- or @name LIKE N'%somepattern%'
    -- or @name IN (SELECT name FROM dbo.BadNameLibrary) 
  BEGIN
    SET @act = 1;
  END
  IF @act = 1
  BEGIN
    ROLLBACK TRANSACTION;
    IF @event = N'CREATE_DATABASE'
    BEGIN
      RAISERROR('You used a bad name [%s].', 11, 1, @name);
      IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @name)
      BEGIN
        EXEC sp_detach_db @dbname = @name;
      END
    END
    ELSE
    BEGIN
      -- name it back
      DECLARE @sql NVARCHAR(MAX) = N'ALTER DATABASE ' 
        + QUOTENAME(@name) + ' MODIFY NAME = ' 
        + QUOTENAME(@oldname) + ';';
      EXEC sp_executesql @sql;
      -- optionally, you could raise an error message
      -- or print something here to let the user know
    END
  END
END
GO

There are some limitations here; for example, it assumes you don't intentionally place characters like =;[] in the database name, and that you don't have a database named [modify-name=] either. I also didn't test what happens if you try to rename a database that already exists (and happens to violate your convention); I envision an infinite loop of database name ping-pong. I suggest you ensure no existing database falls in this category before implementing this trigger; if any databases exist and can't easily be fixed, add exclusions to the logic in the DDL trigger.

Next Steps

Decide on the rules your database names should conform to.
Identify any existing databases that violate your rules.
Implement a DDL trigger which will prevent future databases from violating your conventions.
See the following tips and resources:

About the author

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

View all my tips