By: Aaron Bertrand | Updated: 2014-04-08 | Comments | Related: More > Database Administration
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.
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)','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...
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)','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
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
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)','NVARCHAR(255)')), @event = EVENTDATA().value('(/EVENT_INSTANCE/EventType)','NVARCHAR(255)'), @cmd = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)','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.
- 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:
Last Updated: 2014-04-08
About the author
View all my tips