How can I get the host_name who creates a DB (proactive mode) in SQL Server 2008?

Question

First, I want to clarify that I am not looking for a SELECT HOST_NAME().

I am managing a database environment and have given the developers permission to create the databases they need in the development environment. However, I want to store the machine name (HOST_NAME()) from where the database is created independent of the database user that they use for that purpose. This way, proactively, when a developer creates a database in the future I want to know who creates databases, not only by the owner user but by the name of the machine.

score 3 · Answer 1 · edited Apr 13 '17 at 12:42

That information is available in the default trace (see this answer for more detail), if it hasn't rolled away (the default trace only keeps so much history).

DECLARE @path NVARCHAR(260);

SELECT 
   @path = REVERSE(SUBSTRING(REVERSE([path]), 
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT 
   LoginName,
   HostName,
   ApplicationName,
   SPID,
   StartTime,
   DatabaseName
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 46
AND EventSubClass = 1
AND ObjectID IS NULL
AND ObjectType = 16964
-- AND DatabaseName = N'<database name>';

Other disclaimers:

HostName can't always be collected (e.g. connecting to SQL Server from Visual Studio Code on a Mac leaves HostName NULL).
HostName is easy to spoof.

Going forward, you can do just about anything you want with a DDL trigger, subject to the same disclaimers as above. You can start with something like this:

CREATE DATABASE Watchdog;
GO
USE Watchdog;
GO
CREATE TABLE dbo.CreateDatabaseLog
(
  EventTime datetime2 NOT NULL DEFAULT SYSUTCDATETIME(),
  HostName nvarchar(128),
  EventData XML
);
GO

USE master;
GO
CREATE TRIGGER CaptureDatabaseCreation
ON ALL SERVER
FOR CREATE_DATABASE
AS
  INSERT Watchdog.dbo.CreateDatabaseLog(HostName, EventData)
    SELECT HostName, EVENTDATA();
GO

Now, I'll leave parsing DatabaseName out of the XML as an exercise for the reader, mostly because I am late picking up my kids from daycare. :-)

How can I get the host_name who creates a DB (proactive mode) in SQL Server 2008?

1 Answers1