How to find DB changes ??

Question

How to find DB changes ??

Lavanyasri

SSCarpal Tunnel

Points: 4907
More actions
December 28, 2011 at 9:28 am

#265459

HI ,
If there is any possibility to find the changes was done on database by other users who has sys admin roles.
we had an issue with the database but the DB seems to be good. we are not finding any errors from error log. We thought there may be a data changes in side the DB .
Thanks
lavanya

Viewing 13 posts - 1 through 12 (of 12 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

I'll assume you haven't got any sort of data auditing turned on, otherwise you wouldn't need to ask about it. That's the best way, but that's hindsight for you now.

One possibility is a transaction log parser. Do you have the database in Full recovery (as opposed to Simple)? If so, you might be able to recover transaction data out of a log file or a log backup.

I think ApexSQL and Lumigent produce log parsers. RedGate used to, but I don't think they do any more.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

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

GSquared (12/28/2011)
I think ApexSQL and Lumigent produce log parsers.

At around $1000 for a single license.

RedGate used to, but I don't think they do any more.

They have one for SQL 2000 only.

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

GSquared SSC Guru Points: 260824 More actions · Answer 3

GilaMonster (12/28/2011)
GSquared (12/28/2011)
I think ApexSQL and Lumigent produce log parsers.
At around $1000 for a single license.
RedGate used to, but I don't think they do any more.
They have one for SQL 2000 only.

Yeah, money's potentially an issue. But if parsing the log is the only way to get some important information, it might be a viable option.

Without some sort of auditing mechanism in place, can you think of another way to get "who changed my data"?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

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

GSquared (12/28/2011)
Without some sort of auditing mechanism in place, can you think of another way to get "who changed my data"?

No, and if it was a sysadmin, the log is not an option either. It'll show what and when, but not who. All that goes into the tran log is the SPID (and that only for the BEGIN XACT operation. There's no user or login name. (see example below)

That's part of the log entries from me deleting 10 rows from a table. My login name is Gail and my Server_principal_ID is 261. Neither reflect anywhere in the log (recovering a database doesn't require knowing who made the change)

The only way that could be tied back to a user is if there's an audit history of who logged in when and what their SPID was, then the SPID and transaction start time can be used, but if there's no data auditing, the chances of that existing is slim.

Edit: I tested again with a non-sysadmin login, same result.

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

GSquared SSC Guru Points: 260824 More actions · Answer 5

Exactly.

But if data changes matter to the business, it might be worth digging into. Might not as well. Depends on what they need, why they need it, and how badly they think they need it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 6

If the changes are at schema level then those can be traced with the help of DDL trigger. but neither Gail nor GSquared mentioned it.

Did i overlook something in main issue here ? :unsure:

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

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

Bhuvnesh (12/29/2011)
If the changes are at schema level then those can be traced with the help of DDL trigger. but neither Gail nor GSquared mentioned it.
Did i overlook something in main issue here ? :unsure:

From the OP:

We thought there may be a data changes in side the DB .

Anything (data or DDL) can be audited, but going back after the fact when there's no auditing in place is near-impossible.

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

matt.newman Hall of Fame Points: 3910 More actions · Answer 8

Not sure if the "Just ask" approach was used at all, but it has its' place. Sometimes a non-technical approach will get results. Hopefully there is a limited list of sysadmin people.

select * from sys.syslogins where sysadmin = 1

Then, ask the network admin for a user list for user groups, then ask the users in the list.

Or, send out a collective email asking sysadmin members. Might get a bite.

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 9

matt.newman (12/29/2011)
Not sure if the "Just ask" approach was used at all, but it has its' place. Sometimes a non-technical approach will get results. Hopefully there is a limited list of sysadmin people.
select * from sys.syslogins where sysadmin = 1
Then, ask the network admin for a user list for user groups, then ask the users in the list.
Or, send out a collective email asking sysadmin members. Might get a bite.

If a DBA asks this, I will doubt on his skills.

GSquared SSC Guru Points: 260824 More actions · Answer 10

Dev (12/30/2011)
matt.newman (12/29/2011)
Not sure if the "Just ask" approach was used at all, but it has its' place. Sometimes a non-technical approach will get results. Hopefully there is a limited list of sysadmin people.
select * from sys.syslogins where sysadmin = 1
Then, ask the network admin for a user list for user groups, then ask the users in the list.
Or, send out a collective email asking sysadmin members. Might get a bite.
If a DBA asks this, I will doubt on his skills.

But it's probably better to be honest and say that auditing isn't capturing needed data than to hide it and not get what you need.

I'd rather admit an honest mistake than cover one up. And most managers and co-workers will agree with and sympathize with that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 11

@matt-2 & @Gus: I agree with you. I am just alerting on Management Perspective. Being a pessimistic (DBAs shouldn’t be optimistic) I don’t expect sympathy from management.

matt.newman Hall of Fame Points: 3910 More actions · Answer 12

The best DBA out there cannot account for other people. Even if a DBA does not know what is going on, this would present a learning ability to push to learn "Who can get you fired", by discovering the full extent of users in user groups of logins associated to sysadmin.

If anyone reading has not seen the webinars from Quest with Brent Ozar going over his "Blitz", I'd say it would be good to check it out. Side note, not saying you wouldn't know - just pushing more info out for anyone who has not seen it.

http://www.brentozar.com/blitz/

People walking behind my desk questioned my credibility when watching the webinars from Quest after where Brent talked about his Blitz (the losing wait fast)... heh, worth it though. I'd definitely recommend viewing, even at work! Arie, Kevin, Buck and Brent get a lot of good info in a short time no matter what they're wearing or how demanding on coffee they are.

If asking people "who did what" resolves a problem, and that's all it took - think of how upset that manager would be that's not in your court when they find out you didn't ask because of pride. 😉

I hate telling someone I don't know, but I will follow it with I will do what I can to find out as soon as possible. I see where you are coming from though. Some people will definitely worry about their job, and that is important to them. I stay out of it just that the Blitz link above and sysadmin roles should be examined either way. Might help in the future. 🙂