asked
Auditing SQL Server database configuration changes.
Is there a way to audit changes made to SQL Server configuration settings in SQL SERVER 2005?somehow, my production SQL SERVER database compatibility mode got change from 80 to 90. This caused couple of stored procedures with deprecated =*inner and outer *= join operators to choke.I would like to track down who made the changes and when the changes took place.Can someone point me in the right direction. please?
You have some answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
You can check the [default trace][1]. It's sometimes referred to as the SQL Server flight recorder or black box. It might have the information you want, although, the longer it's been since the event, the less likely this will have any information. [1]:
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2007/06/03/sql-server-s-black-box.aspx
I can't remember whether or not this is an option with SQL Server 2005, but I know it works with 2008 or 2012. From Management Studio Object Explorer, right-click the database, and choose Reports/Standard Reports/Schema Changes History. Check the DDL operations performed at the database level.
Do you have any entries with type "Database" and your database name as the object name? Those would be operations altering the database itself like changing the compatibility mode.
19 People are following this question.
