SQL Server: Audit Security events

Few days ago a customer asked me to implement a security auditing solution for their production instances of SQL Server, to track users and logins management events. I immediately thought about the native SQL Audit function, but there was a problem, all users gain access to SQL Server with the same login, so to identify exactly who I had to capture the hostname field; unfortunately, the available fields in the SQL Server audits are predefined and not mdifiable (at least not easily) and among them the hostname is missing.
At that point I decided to use DDL triggers, which unlike the DML, have not a significant impact in terms of performances.
First of all we have to understand which events intercept, using this query you can have a hierarchical view of available events:

WITH EventsReports (name, PARENT_TYPE, type, level, sort) AS
(
    SELECT CONVERT (varchar (255), type_name), PARENT_TYPE, type, 1, CONVERT (varchar (255), type_name)
    FROM sys.trigger_event_types 
    WHERE PARENT_TYPE  IS NULL
    UNION ALL
    SELECT CONVERT (varchar (255), REPLICATE ( '|', level) + e.type_name),
        e.parent_type, e.type, level + 1,
    CONVERT (varchar (255), RTRIM (sort) + '|' + e.type_name)
    FROM sys.trigger_event_types  as e
        INNER JOIN EventsReports as d
        ON  e.parent_type =  d.type
  )
SELECT PARENT_TYPE, type, name
FROM EventsReports
-- where [name] like '% object%'
ORDER BY sort;

The event name is quite explicative, but in the dubt has a look here

So we decided to track these events: DDL_DATABASE_SECURITY_EVENTS, DDL_DATABASE_EVENTS, DDL_SERVER_SECURITY_EVENTS

Then I have built the DDL trigger.

First let's create a DB and the table that will store our events:

Create DataBase AdminDB;
go
use AdminDB;
go
CREATE TABLE dbo.DDL_Security_Audit_LOG  ( EventId int NOT NULL IDENTITY (1, 1) PRIMARY KEY, EventTime datetime , DbName nvarchar(100), EventType nvarchar(100) , UserName nvarchar(100) ,HostName nvarchar (100) , EventTSQL nvarchar(3000) , Data_XML xml);


Then our DDL Trigger:


Create TRIGGER [DDL_Security_Audit]
ON ALL SERVER
FOR DDL_DATABASE_SECURITY_EVENTS, DDL_DATABASE_EVENTS, DDL_SERVER_SECURITY_EVENTS
AS
BEGIN

Declare @ObjectName nvarchar(100) =  EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)')

Declare @EventType nvarchar(100) =  EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')

Declare @DbName  nvarchar(100) = isnull( EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(1000)'),'Master')

Declare @Command nvarchar(1000) =  EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(1000)')

INSERT INTO AdminDB.dbo.DDL_Security_Audit_LOG
 VALUES ( GETDATE() ,  @DbName, @EventType, suser_sname() , HOST_NAME(), @Command, EVENTDATA()  )

END
GO

Then remember to create a JOB to purge old data (if you want to)

Declare @CutOffDate datetime =  DATEDIFF(d, -30, getdate())
Delete from AdminDB.dbo.DDL_Audit_Security_TABLE_LOG where EventTime < @CutOffDate

Now you have all info you need to monitor your security events at Server and DataBase level.

Just notice in SQL Server there a security hole: no DDL events avalaible for disable Trigger action.

(Thanks to Alessandro, Matteo, Simone and "Rocco" for allowing me to publish this)