In the past back we've I've faced problems with SQL developers or Report developers changing code or accidentally deleting procedures, views, and on rare occasions, tables (not production).  Due to resource limitations we chose not to enable CDC and found an alternate way to track "who did it." Since in most cases no one ever admits to be the cause of a problem, this helps determine who last touched the object. While this process cannot track which specific change was made, it is accurate in defining "who" made the change and "when" it was made.

Since SQL Server logs all schema changes to the default trace file (located in the LOG folder where you installed SQL server), you can create an automated process to read these files file and use the results to "track" who's making schema changes and report them accordingly.

While there can be many ways to implement this type of tracking across many different environments, this scenario focuses on a centralized tracking table, a procedure that gathers the information, an agent job on every server that needs to be monitored, and an agent job on the centralized server that delivers the changes via email.

To implement this type of a solution you need the following items:

1. A tracking table to store the trace file results (preferably on a centralized server)
2. A stored-procedure that fetches data from the traces, and INSERTs it into the tracking table
3. A SQL Agent job that runs on every server you wish to monitor, which executes the procedure you create
4. A SQL Agent job that runs on a schedule, combining all results and delivers the results to your inbox

Step 1 : Create a Tracking Table

Use the DDL below to create the table to centrally store all the schema changes for your servers:

USE [YourDatabase]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DBSchemaChangeMonitor]') AND type in (N'U'))
DROP TABLE [dbo].[DBSchemaChangeMonitor]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DBSchemaChangeMonitor](
[RecID] [bigint] IDENTITY(1,1) NOT NULL,
[Captured] [datetime] NOT NULL,
[Server] [nchar](256) NOT NULL,
[DBName] [nchar](256) NULL,
[Command] [nchar](50) NOT NULL,
[Application] [nchar](500) NULL,
[spid] [int] NOT NULL,
[Object] [nchar](500) NULL,
[Login] [nchar](256) NULL,
[ClientProcessID] [int] NULL,
[WorkStation] [nchar](256) NULL,
[InsertedOn] [date] NULL,
 CONSTRAINT [PK_DBSchemaChangeMonitor] PRIMARY KEY CLUSTERED 
(
[RecID] ASC,
[Captured] ASC,
[Server] ASC,
[Command] ASC,
[spid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Step 2 : Create the "Fetching" stored-procedure

The code below creates the stored-procedure on the target server you wish to monitor. It's important to remember that for each server you create this procedure on, you will need to create a linked server to/from the server hosting the tracking table from Step 1.

USE [YourDatabase]
GO
CREATE PROCEDURE [dbo].[dba_TrackSchemaChanges] ( @Server sysname )AS
/*
----------------------------------------------------------------------------------------------------------------
Purpose: Tracks Schema changes across all DB's, logs to a central table in YourDatabase on YourServer
Department: DBA
Created By: MyDoggieJessie
----------------------------------------------------------------------------------------------------------------
NOTES: 
1. For deployments involving multiple servers that insert into the tracking table, you will need to ensure
you have the appropriate linked servers created; the add the appropriate linked server i.e. ([YourLinkedServer].)
2. This procedure needs to be created on all servers needing to be tracked
----------------------------------------------------------------------------------------------------------------
Modified On: MM/DD/YYYY
Modified By: <<user>>
Changes: 
1. What changed?
----------------------------------------------------------------------------------------------------------------
EXEC dbo.dba_TrackSchemaChanges 'SERVERNAME'
*/--DECLARE VARIABLES
BEGIN
DECLARE @TraceFileName varchar (500)
DECLARE @indx int 
DECLARE @SQL varchar(750)
DECLARE @Cnt int
END
--SET VARIABLES
BEGIN
/* Fetch default trace file path */    SELECT @TraceFileName = PATH FROM sys .traces WHERE is_default = 1
END
--CREATE TEMP TABLE
BEGIN
DECLARE @TmpTrace TABLE (
obj_name nvarchar(256) COLLATE database_default
, database_name nvarchar(256) COLLATE database_default
, start_time datetime
, event_class int
, event_subclass int
, object_type int
, server_name nvarchar(256) COLLATE  database_default
, login_name nvarchar(256) COLLATE  database_default
, application_name nvarchar(256) COLLATE  database_default
, ddl_operation nvarchar(40) COLLATE  database_default
, spid int 
, clipid int
, host nvarchar(40) COLLATE  database_default
)
END
/* ######################################### START MAIN PROCEDURE HERE ########################################## */BEGIN
INSERT INTO @TmpTrace
SELECT 
ObjectName
, DatabaseName
, StartTime
, EventClass
, EventSubClass
, ObjectType
, ServerName
, LoginName
, ApplicationName
, 'temp'
, spid
, ClientProcessID
, HostName
FROM ::fn_trace_gettable( @TraceFileName, DEFAULT )
WHERE 
objecttype not in (21587)
AND EventClass in (46,47,164) 
AND EventSubclass = 0 
AND LoginName NOT IN ('NT AUTHORITY\NETWORK SERVICE', 'sa') 
AND DatabaseID <> 2
AND StartTime NOT IN (SELECT Captured FROM  
[YourLinkedServer].YourDatabase.dbo.DBSchemaChangeMonitor) -- >> Omit previously inserted recs
SET @Cnt = @@ROWCOUNT
/* Process Records */IF @Cnt > 0
BEGIN
/* Update events to be understandable */UPDATE @TmpTrace 
SET ddl_operation = 'CREATE' 
WHERE event_class = 46
UPDATE  @TmpTrace  
SET ddl_operation = 'DROP' 
WHERE event_class = 47
UPDATE  @TmpTrace  
SET ddl_operation = 'ALTER' 
WHERE event_class = 164
/* Fetch the Results */INSERT INTO [YourLinkedServer].YourDatabase.dbo.DBSchemaChangeMonitor (
[Captured]
,[Server]
,[DBName]
,[Command]
,[Application]
,[spid]
,[Object]
,[Login]
,[ClientProcessID]
,[WorkStation]
,[InsertedOn]
)
SELECT
start_time , 
server_name, 
database_name, 
ddl_operation, 
'[' + CAST(object_type as varchar (6)) + '] ' + application_name,
spid, 
obj_name,
login_name,
clipid,
host, 
CONVERT(varchar(10), GETDATE(), 111)
FROM @TmpTrace 
WHERE --object_type not in (21587) -->> This is Table Statistics (not needed)
obj_name NOT IN ('Tables you want to omit') -->> Remove if you want ALL tables
ORDER BY 
server_name, start_time DESC
END
END
/* ########################################## END MAIN PROCEDURE HERE ########################################### */

If you are not sure how to properly create a linked server, please read about it http://msdn.microsoft.com/en-us/library/ms190479(v=sql.100).aspx here.

Step 3 : Create a SQL Agent job to exexcute the procedure

Automation can be a beautiful thing.  To get your profile trace data into the table auto-magically you need to create a job that executes your dba_TrackSchemaChanges procedure on a scheduled basis.

This is a very simple job, with a single step that executes:

EXEC dbo.dba_TrackSchemaChanges @@SERVERNAME

You can schedule this job to run at your specified frequency to capture changes. The code below can be used to create the agent job and will set it up to run daily @ 5-minute intervals:

USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Data Collector' AND category_class=1)
BEGIN
 EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Data Collector'
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job
 @job_name=N'MAINT - Monitor Schema Changes', 
 @enabled=1, 
 @notify_level_eventlog=0, 
 @notify_level_email=2, 
 @notify_level_netsend=0, 
 @notify_level_page=0, 
 @delete_level=0, 
 @description=N'MyDoggieJessie - 09/29/2011 - Monitors all Schema changes for each database on the server, logs to a central table on [YOURSERVER].YOURDATABASE.', 
 @category_name=N'Data Collector', 
 @owner_login_name=N'YourServiceAccount', 
 @notify_email_operator_name=N'DBA', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Check for Changes', 
  @step_id=1, 
  @cmdexec_success_code=0, 
  @on_success_action=1, 
  @on_success_step_id=0, 
  @on_fail_action=2, 
  @on_fail_step_id=0, 
  @retry_attempts=0, 
  @retry_interval=0, 
  @os_run_priority=0, @subsystem=N'TSQL', 
  @command=N'EXEC dbo.dba_TrackSchemaChanges @@SERVERNAME', 
  @database_name=N'YOURDATABASE', 
  @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every 5 minutes', 
  @enabled=1, 
  @freq_type=4, 
  @freq_interval=1, 
  @freq_subday_type=4, 
  @freq_subday_interval=5, 
  @freq_relative_interval=0, 
  @freq_recurrence_factor=0, 
  @active_start_date=20110929, 
  @active_end_date=99991231, 
  @active_start_time=0, 
  @active_end_time=235959, 
  @schedule_uid=N'e664f30d-090a-4fde-9911-c37e38100201'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Step 4 : Create a SQL Agent job to deliver the results

Now that you have data (hopefully there's not too much), you should consider setting up an automated notification that delivers and email to the DBA team; outlining all the changes for the prior day.

To do so, you will need to create another simple SQL Agent Job that gathers the data from your centralized tracking table, formats it, and then sends it via email.

Again, there are many ways to accomplish this, but the method I used is outlined below (you will need to edit this to use your own database, and linkedserver names):

    SET NOCOUNT ON;
    DECLARE @Date date = GETDATE() - 1
    DECLARE @Results TABLE (
    idx int IDENTITY(1,1), 
    ServerName varchar(25), 
    DBName varchar(25), 
    Cmd varchar(10), 
    AppName varchar(250), 
    ObjectName varchar(500), 
    Who varchar(100))
    ; WITH Changes AS (
    SELECT DISTINCT 
    'LINKED SERVER NAME' [Server],
    [DBName],
    [Command],
    [Application],
    [Object],
    [Login],
    [InsertedOn]
    FROM
    [YOURDATABASE].[dbo].[DBSchemaChangeMonitor]
    WHERE
    Server = 'LINKED SERVER NAME'
    AND LOGIN NOT IN ('Example: Service accounts, sa, or yourself!')
    --AND (DBName LIKE 'ABC%' OR DBName LIKE 'XYZ%') -->> Needed only for filtering databases
    AND InsertedOn >= @Date
    UNION ALL
    SELECT DISTINCT 
    'ADDITIONAL LINKED SERVER NAME' [Server],
    [DBName],
    [Command],
    [Application],
    [Object],
    [Login],
    [InsertedOn]
    FROM
    [YOURDATABASE].[dbo].[DBSchemaChangeMonitor]
    WHERE
    Server = 'LINKED SERVER NAME'
    AND LOGIN NOT IN ('Example: Service accounts, sa, or yourself!')
    --AND (DBName LIKE 'ABC%' OR DBName LIKE 'XYZ%') -->> Needed only for filtering databases
    AND InsertedOn >= @Date
    UNION ALL
    .
    .
    .
    )
    INSERT INTO @Results
    SELECT DISTINCT [Server],DBName,Command,[Application], [Object], [Login] FROM Changes
    DECLARE @idx int, @ServerName varchar(25), @DBName varchar(25), @Cmd varchar(10), @AppName varchar(250), 
    @ObjectName varchar(500), @Who varchar(100), @Subject varchar(250), @Body varchar(MAX)
    SET @Subject ='Past 24-hour schema changes (that DBA''s care about)'
    SET @Body = '<div style="font-size:12px;font-family:Verdana"><strong>Here at the schema changes which ocurred in the past 24 hours</strong><div style="height:20px;background: #fff url(aa010307.gif) no-repeat scroll center;">
    <hr style="display:none;" /></div></div>'
    WHILE (SELECT COUNT(1) FROM @Results) > 0
    BEGIN 
    SELECT TOP 1 @idx = idx, @ServerName = ServerName, @DBName = DBName, @Cmd =Cmd, @AppName = AppName, 
    @ObjectName = ObjectName, @Who = Who FROM @Results
    SET @Body = @Body + '<p style="font-size:12px;font-family:Verdana">'
    + 'Server: ' + @ServerName + ', Database: ' + @DBName + ', <font color="red">' + @Cmd + ' by ' + @Who + '</font>, '
    + 'Object: ' + @ObjectName 
    DELETE FROM @Results WHERE idx = @idx
    END
    IF (SELECT COUNT(1) FROM @Results) > 0
     EXEC msdb..sp_send_dbmail @recipients = 'dba@yourcompany.com', @Subject = @Subject, @body = @body, @body_format = 'HTML'

To create the job with the code above, you may use the following code (you will need to edit this to use your own database, and linkedserver names):

      USE [msdb]
      GO
      BEGIN TRANSACTION
      DECLARE @ReturnCode INT
      SELECT @ReturnCode = 0
      IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Data Collector' AND category_class=1)
      BEGIN
      EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Data Collector'
      IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
      END
      DECLARE @jobId BINARY(16)
      EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DBA - Daily company-wide schema change notification', 
      @enabled=1, 
      @notify_level_eventlog=0, 
      @notify_level_email=2, 
      @notify_level_netsend=0, 
      @notify_level_page=0, 
      @delete_level=0, 
      @description=N'MyDoggieJessie - 8/25/2014 - Created to send a daily email to the DBA team to show company-wide schema changes at 11:59pm', 
      @category_name=N'Data Collector', 
      @owner_login_name=N'sa', 
      @notify_email_operator_name=N'DBA', @job_id = @jobId OUTPUT

      IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
       EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Prepare HTML email', 
      @step_id=1, 
      @cmdexec_success_code=0, 
      @on_success_action=1, 
      @on_success_step_id=0, 
      @on_fail_action=2, 
      @on_fail_step_id=0, 
      @retry_attempts=0, 
      @retry_interval=0, 
      @os_run_priority=0, @subsystem=N'TSQL', 
      @command=N'SET NOCOUNT ON;
      DECLARE @Date date = GETDATE() - 1
      DECLARE @Results TABLE (
      idx int IDENTITY(1,1), 
      ServerName varchar(25), 
      DBName varchar(25), 
      Cmd varchar(10), 
      AppName varchar(250), 
      ObjectName varchar(500), 
      Who varchar(100))
      ; WITH Changes AS (
      SELECT DISTINCT 
      ''LINKED SERVER NAME'' [Server],
      [DBName],
      [Command],
      [Application],
      [Object],
      [Login],
      [InsertedOn]
      FROM
      [YOURDATABASE].[dbo].[DBSchemaChangeMonitor]
      WHERE
      Server = ''LINKED SERVER NAME''
      AND LOGIN NOT IN (''Example: Service accounts, sa, or yourself!'')
      --AND (DBName LIKE ''ABC%'' OR DBName LIKE ''XYZ%'') -->> Needed only for filtering databases
      AND InsertedOn >= @Date
      UNION ALL
      SELECT DISTINCT 
      ''ADDITIONAL LINKED SERVER NAME'' [Server],
      [DBName],
      [Command],
      [Application],
      [Object],
      [Login],
      [InsertedOn]
      FROM
      [YOURDATABASE].[dbo].[DBSchemaChangeMonitor]
      WHERE
      Server = ''LINKED SERVER NAME''
      AND LOGIN NOT IN (''Example: Service accounts, sa, or yourself!'')
      --AND (DBName LIKE ''ABC%'' OR DBName LIKE ''XYZ%'') -->> Needed only for filtering databases
      AND InsertedOn >= @Date
      UNION ALL
      .
      .
      .
      )
      INSERT INTO @Results
      SELECT DISTINCT [Server],DBName,Command,[Application], [Object], [Login] FROM Changes
      DECLARE @idx int, @ServerName varchar(25), @DBName varchar(25), @Cmd varchar(10), @AppName varchar(250), 
      @ObjectName varchar(500), @Who varchar(100), @Subject varchar(250), @Body varchar(MAX)
      SET @Subject =''Past 24-hour schema changes (that DBA''''s care about)''
      SET @Body = ''<div style="font-size:12px;font-family:Verdana"><strong>Here at the schema changes which ocurred in the past 24 hours</strong><div style="height:20px;background: #fff url(aa010307.gif) no-repeat scroll center;">
      <hr style="display:none;" /></div></div>''
      WHILE (SELECT COUNT(1) FROM @Results) > 0
      BEGIN 
      SELECT TOP 1 @idx = idx, @ServerName = ServerName, @DBName = DBName, @Cmd =Cmd, @AppName = AppName, 
      @ObjectName = ObjectName, @Who = Who FROM @Results
      SET @Body = @Body + ''<p style="font-size:12px;font-family:Verdana">''
      + ''Server: '' + @ServerName + '', Database: '' + @DBName + '', <font color="red">'' + @Cmd + '' by '' + @Who + ''</font>, ''
      + ''Object: '' + @ObjectName 
      DELETE FROM @Results WHERE idx = @idx
      END
      IF (SELECT COUNT(1) FROM @Results) > 0
       EXEC msdb..sp_send_dbmail @recipients = ''dba@yourcompany.com'', @Subject = @Subject, @body = @body, @body_format = ''HTML''
      ', 
      @database_name=N'master', 
      @flags=4
      IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
      EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
      IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
      EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily', 
      @enabled=1, 
      @freq_type=4, 
      @freq_interval=1, 
      @freq_subday_type=1, 
      @freq_subday_interval=0, 
      @freq_relative_interval=0, 
      @freq_recurrence_factor=0, 
      @active_start_date=20140825, 
      @active_end_date=99991231, 
      @active_start_time=235950, 
      @active_end_time=235959, 
      @schedule_uid=N'9b082f08-0669-4583-86b3-c83eeb09e2d6'
      IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
      EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
      IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
      COMMIT TRANSACTION
      GOTO EndSave
      QuitWithRollback:
          IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
      EndSave:
      GO
    

Conclusion

Tracking schema changes can be cumbersome when you manage 20+ servers - IMHO having any solution in place that saves you any amount of time on a daily basis is a plus.  With just a little bit of planning and some straightforward coding, you can easily put a process in place that delivers alerts to you minutes after they occur.

This solution definitely isn't elegant and I'm positive there are countless ways to improve upon it (all suggestions/criticisms welcomed) but it does work quite well, and it is FREE

I hope it helps someone else out as much as it's helped me over the past few years - Enjoy!

Other considerations 

Growth: Since this process only logs data to a centralized location in efforts to keep your data footprint small, you might want to add a step that "prunes" data over time - this will keep your table easier to manage.