question

zillabaug avatar image
zillabaug 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?
sql-server-2005securityauditmonitoring
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab avatar image
Tom Staab answered
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.
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Tom. Your suggested approach only shows me schema changes to database objects. what I looking for is changes to the database properties i.e the compatibility mode to be specific.
0 Likes 0 ·
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.
0 Likes 0 ·
None of the above. It only give me the current schema changes report. It doesn't show any history of changes made yesterday and beyond.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.