alter tables default auditing - who deleted my columns?

Question

alter tables default auditing - who deleted my columns?

baby_cheeses

Old Hand

Points: 355
More actions
October 25, 2010 at 6:51 am

#147393

I've inhereted a sql db with rather lapse permissions.
Someone has removed 2 columns from 1 of the tbls. Due to the permissions problem (that I am politically powerless to change) it could be 1 of about 53 muppets.
Is there any way I can find out which user did this?
There are no event triggers / no auditing that is not default.
Thanks in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

yes, if not too much time has passed, you can look at the default trace to see who did any DDL chances(ALTER/CREATE/DROP of objects.

easiest way is via the SSMS GUI, which filters the default trace on a per-database level:

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 2

The only other way that might work is with a log reader tool, but you would have to purchase them.

The default trace is what I'd suggest first.

baby_cheeses Old Hand Points: 355 More actions · Answer 3

Genius. Thank you!! I didn't know about that report & had forgotten completely about the default trace. It's brill. Thank you.

Unfortunately, in this case, it seems that too much time has passed.

Is there any other way to look back further?

baby_cheeses Old Hand Points: 355 More actions · Answer 4

Doh, just posted my reply when I save Steve had replied also re: the log reader and answered the question.

Thank you both for your replies. I've learnt a fair bit today!! The client will back off when I mention money for buying software and forget all about it.

Sigh.

🙂

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 5

There's no other way. If permissions will allow people to add/remove columns and you cannot handle this administratively (manager talking to them), then you're stuck.

Ultimately this is a reason to build a case that removes permissions. Show the rework, the loss of work, the time to finish something, etc. and then explain why someone altering the schema causes issues.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Steve Jones - SSC Editor (10/25/2010)
The only other way that might work is with a log reader tool, but you would have to purchase them.

Plus it's unlikely that the log will answer the 'who' question. It'll tell you what was done and when, but it doesn't contain login information (not necessary for the purposes of the transaction log)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Consider sticking a DDL trigger on the DB to catch all schema changes in future. It's not intrusive.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass