SQL Server - Who made DDL table changes on the database.

26 April,2012 by Jack Vamvas

Question:

Some DDL changes have occurred on the SQL Server database. Can I find out who made the changes?

Answer:

Yes. The SQL Server default trace has the Object Altered event. 

Read this FAQ for details on SQL default trace

This script will list all Object Altered event. Add WHERE predicates on date and databasename to refine  the search and isolate the DDL change

 

select e.name as eventclass,
t.loginname, 
t.spid, 
t.starttime,
t.textdata, 
t.objectid, 
t.objectname, 
t.databasename, 
t.hostname, 
t.ntusername, 
t.ntdomainname, 
t.clientprocessid, 
t.applicationname, 
t.error 
FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1f.[value] 
FROM sys.fn_trace_getinfo(NULL) f WHERE f.property = 2)), DEFAULT) T 
inner join sys.trace_events e on t.eventclass = e.trace_event_id 
where eventclass=164

 Read Also

ISO-11179 Naming Conventions and SQL DDL - SQL Server DBA

How to request SQL Server troubleshooting

SQL Server – Find last time of update on a table - SQL Server DBA


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server - Who made DDL table changes on the database.


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer