Information on a Dropped...Something

A colleague of mine found that a view that he needed had been dropped, or seriously altered...and then it came back. If I sound vague about what happened, it's because I am. I'll know a bit more tomorrow, but I have a more general question.

I went looking through some log files and found very little, aside from the fact that the view had been last updated sometime in the early morning. The view is just a restriction of data from a table found in a different database, so the issue may have been that the other database became inaccessible, or the source table was dropped, or the view was dropped, or the source table was altered such that the view was no longer meaningful.

There is no specific logging we've added that looks for who/when alterations to tables are made (aside from adding/updating records in the table). So, all I have to go on is whatever comes with SQL Server. However, there is a whole lot of information that gets logged, and I don't know it all that well. So, what I'm looking for is any suggestions as to how to try to figure out what happened. There are very few of us who should be able to alter either the source of the view. Some are away, some have no interest in the database in question, and so on. We've basically ruled everybody out, which means that somebody probably did something without realizing they had.

Any suggestions on how to try to track this down?

What version of SQL Server? The log files that you said you looked at, have you looked further back in the past to see if this type of thing has ever happened before?

I didn't include the version because I wasn't sure. I use a much more recent version than my office does, and I couldn't remember which one they were using. I believe it's 2012, so not utterly ancient, but not up to date. I'm using 2017...or is it 2019, I'll have to look sometime, because I realize now that I've flat out forgotten.

The log files I was looking at appear to be truncated. There's some backup software, Redgate I believe, which runs pretty frequently, and it looks like it is trimming the logs. Some of the events only went back to the day before, and none went back more than a few months.

I may be looking at the wrong thing, or looking in the wrong place. I have not delved into the log files as a matter of course, and don't really know my way around very well. I would probably benefit from a straight up description from somebody as to how they would look at something like this. What tools? How? etc.

Do you have transaction logs for the database? If in full recovery you could use SQL tools to look though them. The default trace might have something if it was not done long ago

Do you source control your db objects, or at least the scripts for them? I'm curious as to whether there's a stored proc someone's written that''s supposed to drop/create a temp view or something, and they forgot a # or ## and it's dropping/creating the view - and the name happens to be a coincidence. OR... someone thinks they're being clever and wrote a stored proc to intentionally drop the view and recreate it to flush the cached execution plan -- yes, I've seen this done before.

-tg

If you want to find out who connected and when, google for this:

"ms sql server trace auditing for login success and failure"

Quote:

---

Originally Posted by techgnome

Do you source control your db objects, or at least the scripts for them? I'm curious as to whether there's a stored proc someone's written that''s supposed to drop/create a temp view or something, and they forgot a # or ## and it's dropping/creating the view - and the name happens to be a coincidence. OR... someone thinks they're being clever and wrote a stored proc to intentionally drop the view and recreate it to flush the cached execution plan -- yes, I've seen this done before.

-tg

---

To the first question, no. We're far too informal. Too informal for that, and probably too informal in general.

We think somebody did something bad, and we think we know who, but they aren't fessing up.

Quote:

---

Originally Posted by szlamany

If you want to find out who connected and when, google for this:

"ms sql server trace auditing for login success and failure"

---

Thanks for that, I'll take a look on Monday.