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?

more ▼

asked Oct 08, 2015 at 06:47 PM in Default

avatar image

481 3 7 15

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.

Oct 12, 2015 at 08:31 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

You can check the default trace. 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.

more ▼

answered Oct 09, 2015 at 10:25 AM

avatar image

Grant Fritchey ♦♦
137k 20 46 81

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 08, 2015 at 07:04 PM

avatar image

Tom Staab ♦
14.5k 7 15 21

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.

Oct 08, 2015 at 07:21 PM zillabaug

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.

Oct 08, 2015 at 07:58 PM Tom Staab ♦

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.

Oct 08, 2015 at 08:15 PM zillabaug
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 08, 2015 at 06:47 PM

Seen: 336 times

Last Updated: Jan 09, 2016 at 07:30 AM

Copyright 2018 Redgate Software. Privacy Policy