I have been reading a lot about MSSQL 2012 lately, to get myself ready for when the developers and application admins find out about the new features and start nagging about having servers upgraded to the latest and (possibly) greatest. 
I am usually very skeptical when it comes to upgrading to a new version of enterprise software within the first 6 months of its release, especially so if the current version works perfectly fine as is. So far I haven’t read anything to quell my skepticism, but sooner or later I guess I have to face the music and start upgrading or migrating databases. The good thing though about reading documentation is the rediscovery of forgotten features and possibilities. Sometimes it is nice to just let the mind take a random road trip through the interwebs, just to find out were it winds up. This time, I got reacquainted with DML triggers and events, and remembered a long forgotten scheming plan to track who was tampering with MY databases, which is the main focal point of this post.
I would guess most DBA’s have had a long days at work troubleshooting misbehaving applications, just to discover after hours of tedious troubleshooting and tracing that the database schema has been altered by the application and thereby caused the systems own demise. Maybe “someone” added a select * to a stored procedure, failing to recognize the fact that running said procedure against a production table containing 500K records instead of the development version containing 5 records might cause a tiny drop in performance 
. In those cases, wouldn’t it be nice to know when the schema was last changed? And more importantly, who did it? Sometimes the database is used by a program written by an external vendor (who of course demands dbowner permissions), and the change may happen without anyone knowing before the users starts complaining. It is always nice to point the finger at someone else as the root cause, but more importantly it gets a lot easier to identify problems when you can correlate schema changes and user complaints.
There are of course commercial products available that will gladly create an audit log, in exchange for a hefty up front payment and a princely yearly allowance. If you have in-house developers, source control for the database is a good option, especially so since developers are used to working with source control and you might already have the aforementioned software in place. Limiting access to the databases is also smart, and should be implemented anyway, but sadly there are a lot of systems out there requiring dbowner, or even worse sysadmin permissions by design. One time I had to install a system were the vendor’s install doc in great detail explained how to enable the “sa” account and give it a blank password 
The script
I have written a script that creates a TamperLog table in a separate database, and then adds a simple system trigger who will log all database and server instance DDL events. You may want to limit this if you have an active environment with lots of changes happening. For instance, some system stored procedures will trigger a DDL event.
The trigger I wrote latches on to every database on the instance, but it is possible to create separate triggers for each database, and you can easily create a separate tamper log for each database if you like. Not all DML events are available in both scopes. For more information, see http://msdn.microsoft.com/en-us/library/bb522542(v=sql.105).aspx were you can also find a script to list the events that are available on your server.
I have added a primary key and index to the log, mostly because the voices in my head gets angry when I create heaps 
, but also in case I have to clean out parts of the table.
You should be aware of the fact that if the trigger fails for some reason, lets say the log table runs out of disk space, it will cause all operations who trigger a monitored event to raise an error.
Create Database AuditLog;
GO
--Create tamper log table
USE AuditLog;
GO
Create Table dbo.TamperLog(
TamperLogID int Identity(1,1) not null,
EventTime DATETIME,
EventType VarChar(50),
LoginName VARCHAR(50),
UserName VARCHAR(50),
DatabaseName VARCHAR(100),
SchemaName VARCHAR(50),
ObjectName VARCHAR(100),
ObjectType VARCHAR(100),
TSQLCommand VARCHAR(MAX)
);
GO
ALTER TABLE dbo.TamperLog ADD CONSTRAINT
PK_TamperLog PRIMARY KEY CLUSTERED
(
TamperLogID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
GO
/*Creates a server tamper trigger that logs all DDL events. You migth want to change what events are logged. */
IF EXISTS (SELECT * FROM sys.server_triggers
WHERE name = 'LCS_DDL_ServerTamperTrigger')
DROP TRIGGER LCS_DDL_ServerTamperTrigger ON ALL SERVER;
GO
CREATE TRIGGER LCS_DDL_ServerTamperTrigger
ON All Server
FOR DDL_EVENTS
AS
DECLARE @eventData XML
SET @eventData = EVENTDATA()
INSERT INTO AuditLog.dbo.TamperLog VALUES
(
REPLACE(CONVERT(VARCHAR(50), @eventData.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),
CONVERT(VARCHAR(50), @eventData.query('data(/EVENT_INSTANCE/EventType)')),
CONVERT(VARCHAR(50), @eventData.query('data(/EVENT_INSTANCE/LoginName)')),
CONVERT(VARCHAR(50), @eventData.query('data(/EVENT_INSTANCE/UserName)')),
CONVERT(VARCHAR(100),@eventData.query('data(/EVENT_INSTANCE/DatabaseName)')),
CONVERT(VARCHAR(50), @eventData.query('data(/EVENT_INSTANCE/SchemaName)')),
CONVERT(VARCHAR(100), @eventData.query('data(/EVENT_INSTANCE/ObjectName)')),
CONVERT(VARCHAR(100), @eventData.query('data(/EVENT_INSTANCE/ObjectType)')),
Replace(CONVERT(VARCHAR(MAX), @eventData.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)')), '
', char(13))
);
GO
