OP here is how I have set-up custom auditing on the Server. The script given here will create an Audit Table and try to record DB access information. I use this to track when was the last time any user has accessed the DB and if the DB is not accessed for more than 15 days, I shall take that DB offline.

USE master

GO

-- ----------------------------------------------

-- Create AuidtTable if it does not exist

-- ----------------------------------------------

IF Object_id('AuditTable') IS NULL

CREATE TABLE AuditTable

(

DatabaseName VARCHAR(500),

last_access_time DATETIME,

no_of_days INT,

last_upd_dtmDATETIME

);

GO

-- ----------------------------------------------

-- Insert entries from all DB's.

-- ----------------------------------------------

INSERT master..AuditTable

SELECT name,

Getdate(),

0,

Getdate()

FROM sys.databases

WHERE name NOT IN ( 'master', 'tempdb', 'model', 'msdb' ) ;

GO

-- ----------------------------------------------

-- Update audit table for the first time

-- ----------------------------------------------

WITH

Base

AS (SELECT Db_name(database_id) DatabaseName,

last_user_seek AS last_access_time

FROM sys.dm_db_index_usage_stats

UNION ALL

SELECT Db_name(database_id) DatabaseName,

last_user_scan

FROM sys.dm_db_index_usage_stats

UNION ALL

SELECT Db_name(database_id) DatabaseName,

last_user_lookup

FROM sys.dm_db_index_usage_stats

UNION ALL

SELECT Db_name(database_id) DatabaseName,

last_user_update

FROM sys.dm_db_index_usage_stats),

AccessInfo

AS (SELECT DatabaseName,

Max(last_access_time) last_access_time,

Datediff(dd, Max(last_access_time), Getdate()) AS no_of_days

FROM Base

GROUP BY DatabaseName

HAVING DatabaseName NOT IN ('master', 'tempdb', 'model', 'msdb'))

MERGE master..AuditTable AS tgt

USING (SELECT DatabaseName,last_access_time,no_of_days,getdate() last_upd_dtm FROM AccessInfo) AS src

ON tgt.databasename = src.databasename

WHEN MATCHED

THEN

UPDATE SET tgt.last_access_time = src.last_access_time, tgt.no_of_days = src.no_of_days, tgt.last_upd_dtm = src.last_upd_dtm

WHEN NOT MATCHED BY TARGET

THEN

INSERT (DatabaseName,last_access_time,no_of_days,last_upd_dtm)

VALUES ( src.DatabaseName,src.last_access_time,src.no_of_days,src.last_upd_dtm);

GO

Hope you can modify this in your own way.