How can I get the host_name who creates a DB (proactive mode) in SQL Server 2008? 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.
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'&lt;database name&gt;';


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. :-)