4

I am trying to find out who is the person who did a specific change on the Microsoft SQL 2008 R@ database structure (I have a procedure which was altered).. given I have a DBO access, would that be possible? Any kind of logs to show me the SQL/Windows logon usernames of the people who did changes, when they did them, and what changes they made? Would such log be available with the IT Administrator of the server? What is it named if any?

Thanks!

JoHa
  • 1,989
  • 10
  • 28
  • 42
  • 2
    See http://stackoverflow.com/questions/5579223/how-to-check-date-of-last-change-in-stored-procedure-or-function-in-sql-server - it tells you when, but not who. It might be good to start putting your database under source control. – Sir Crispalot Nov 03 '11 at 10:49
  • 1
    If you have the when and successful logins are being recorded by SQL, you might be able to narrow it down. – Andrew Nov 03 '11 at 10:52

2 Answers2

1

Short answer is you can’t see historical data for updates unless you already had some auditing system in place at the time change happened.

What you can do is to try reading transaction log using some third party tool like ApexSQL Log and checking if there is info like this written somewhere in t. log.

I know there is a lot of historical data written in transaction log that can be used for auditing but I’m not 100% sure about finding username.

If you want to setup a system that will track this going forward you can try DDL triggers, SQL Traces and more…

Phill C
  • 606
  • 7
  • 6
0

It might be possible to use the default trace. Check out the following blog:

http://www.eraofdata.com/blog/tag/default-trace/

Scroll down to "Example 5: Finding out who made changes"

David Atkinson
  • 5,759
  • 2
  • 28
  • 35