Other than DDL triggers and change data capture, is there any way to track history of DDL changes in SQL server. I also want to know the client IP which made the respective DDL change. Kindly guide me .
2 Answers
I have used this script from David Rueter, its pretty good have changelog script used and login name also.
https://github.com/DavidRueter/SQLVer/ http://www.sqlservercentral.com/articles/Version+Control+Systems+(VCS)/119029/
Here's a start from https://www.simple-talk.com/sql/database-administration/auditing-ddl-changes-in-sql-server-databases/:
There are plenty of ways of monitoring DDL changes. You can set up DDL triggers to output changes to some set of tables. You could set up a task to monitor the default trace and try to capture changes into a table again. You could use Profiler to set up a server-side trace and capture events that show structural changes which output to a file. You might, if you’re feeling terribly smart and brave, use Extended Events. All of these are ‘intrusive’ in that they require actions that make changes on the server and possibly impact performance.
We can discuss details of any that might be an option in your scenario.
By the way, DDL triggers can actually capture the client IP using this:
SELECT client_net_address
FROM sys.dm_exec_connections
WHERE Session_id = @@SPID;
