Here is some script I put together from a few sources that will create triggers on all your dbs, create an audit database and setup some some permissions. It may or may not work, use at your own risk.
CREATE DATABASE DBA_AUDIT
GO
USE DBA_AUDIT
GO
CREATE TABLE AuditLog
(ID INT PRIMARY KEY IDENTITY(1,1),
Command NVARCHAR(1000),
PostTime DATETIME,
HostName NVARCHAR(100),
LoginName NVARCHAR(100),
DatabaseName NVARCHAR(100)
)
GO
CREATE ROLE AUDITROLE
GO
sp_adduser 'guest','guest','AUDITROLE'
GO
GRANT INSERT ON SCHEMA::[dbo]
TO AUDITROLE
--CREATE TRIGGER IN ALL NON SYSTEM DATABASES
DECLARE @dataname varchar(255),
@dataname_header varchar(255),
@command VARCHAR(2000),
@usecommand VARCHAR(100)
--SET @command = '';
DECLARE datanames_cursor CURSOR FOR SELECT name FROM sys.databases
WHERE name not in ('master', 'pubs', 'tempdb', 'model','msdb')
OPEN datanames_cursor
FETCH NEXT FROM datanames_cursor INTO @dataname
WHILE (@@fetch_status = 0)
BEGIN
PRINT '----------BEGIN---------'
--PRINT 'DATANAME variable: ' + @dataname;
--EXEC ('USE ' + @dataname);
--PRINT 'CURRENT db: ' + db_name();
SET @command = 'DECLARE @my_cmd NVARCHAR(2000); SET @my_cmd = ''CREATE TRIGGER DBA_Audit ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
DECLARE @cmd NVARCHAR(1000)
DECLARE @posttime NVARCHAR(24)
DECLARE @spid NVARCHAR(6)
DECLARE @loginname NVARCHAR(100)
DECLARE @hostname NVARCHAR(100)
DECLARE @dbname NVARCHAR(100)
SET @data = EVENTDATA()
SET @cmd = @data.value(''''(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]'''', ''''NVARCHAR(1000)'''')
SET @cmd = LTRIM(RTRIM(REPLACE(@cmd,'''''''','''''''')))
SET @posttime = @data.value(''''(/EVENT_INSTANCE/PostTime)[1]'''', ''''DATETIME'''')
SET @spid = @data.value(''''(/EVENT_INSTANCE/SPID)[1]'''', ''''nvarchar(6)'''')
SET @loginname = @data.value(''''(/EVENT_INSTANCE/LoginName)[1]'''',
''''NVARCHAR(100)'''')
SET @hostname = HOST_NAME()
SET @dbname = @data.value(''''(/EVENT_INSTANCE/DatabaseName)[1]'''', ''''NVARCHAR(100)'''')
INSERT INTO [DBA_AUDIT].dbo.AuditLog(Command, PostTime,HostName,LoginName,DatabaseName)
VALUES(@cmd, @posttime, @hostname, @loginname, @dbname);''; EXEC [' + @dataname + ']..sp_executesql @my_cmd'
PRINT @command
EXEC (@command);
FETCH NEXT FROM datanames_cursor INTO @dataname;
PRINT '----------END---------'
END
CLOSE datanames_cursor
DEALLOCATE datanames_cursor
/*THIS SECTION REMOVES AUDITING TRIGGER*/
--sp_msforeachdb @command1='USE [?]; DROP TRIGGER [DBA_Audit] ON DATABASE'
/*THIS SECTION TOTALLY REMOVES AUDITING*/
--DROP DATABASE [DBA_AUDIT]
--DROP TABLE AUDIT_LOG
/* THIS TESTS THE AUDITING
USE [SQL_DBA]
EXECUTE AS LOGIN = 'SQL_DBA_REPORTS'
CREATE TABLE TEST1
(ID INT PRIMARY KEY IDENTITY(1,1),
Command NVARCHAR(1000),
PostTime DATETIME,
HostName NVARCHAR(100),
LoginName NVARCHAR(100),
DatabaseName NVARCHAR(100)
)
GO
*/