question

artistlover avatar image
artistlover asked

Changes to DB

I need to log all changes to a database. Someone is changing some times and no one is owning up to it. Can someone please direct me on the best way to do this. It is sql server 2008 and it is one database that i need to monitor.
sql-server-2008logging
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

its structure that is changing not data
0 Likes 0 ·
Any news? Just wondering if you've managed to find the perpetrator yet...
0 Likes 0 ·
mjharper avatar image
mjharper answered
If it's changes to the schema you're interested in there were some good answers [here][1] that you might want to look at. [1]: http://ask.sqlservercentral.com/questions/96676/when-was-table-one-column-removed-from-table-in-sq.html
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
You can use the [default trace][1] to look at structural changes to the server and databases. If it's data changes you're having problems with, then there's no way to check for data changes in a default install. You would have to set something up to audit changes ahead of time, like [change data capture][2], or using [extended events][3] to observe the queries that are passed to the system. The closest you could come to this would be to look at the queries in cache to see what has been called using dynamic management objects (DMO) such as sys.dm_exec_query_stats combined with sys.dm_exec_sql_text. But, those will only show an aggregate of queries called. They won't show who called them or, if parameters are used, what the values of the parameters are. For data changes, it's CDC or extended events to solve the problem. For what you're referring to, I'd suggest the extended events. [1]: http://msdn.microsoft.com/en-us/library/ms175513.aspx [2]: http://msdn.microsoft.com/en-us/library/bb522489(v=SQL.105).aspx [3]: http://msdn.microsoft.com/en-us/library/bb630282.aspx
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You can also look at the Schema Change History report for schema changes, which from my understanding utilizes the default trace but a much easier way to obtain the data. Just throwing that out there.
0 Likes 0 ·
How do i set up trace?
0 Likes 0 ·
actually i got the trace going. Can i clear it and watch it from now on?
0 Likes 0 ·
Are you using Profiler connected to your production machine? That's a little dangerous, just so you know. If the system is under load, the Profiler GUI can cause memory errors. Best to run as a server side trace. I think this article by Jonathan Kehayias shows how: http://www.simple-talk.com/sql/performance/a-performance-troubleshooting-methodology-for-sql-server/
0 Likes 0 ·
SirSQL avatar image
SirSQL answered
Alternatively if you are using Enterprise Edition you can set up SQL audits to capture any changes to the data.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

askmlx121 avatar image
askmlx121 answered
hi artistover use default trace see below kindly run the first query and copy the "c:/programmefies/.trn" and paste it to second query and run the second query and see it.........hope it may help....... SELECT * FROM ::fn_trace_getinfo(default) SELECT top 15 loginname, textdata, RoleName, TargetLoginName, starttime, e.name as EventName, hostname, applicationname, servername, databasename, objectName, e.category_id, cat.name, duration, eventclass, eventsubclass, loginsid, endtime, spid FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_4192.trc',0) INNER JOIN sys.trace_events e ON eventclass = trace_event_id INNER JOIN sys.trace_categories AS cat ON e.category_id = cat.category_id where e.name='Audit Add Login to Server Role Event' order by starttime desc
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered
As Grant Fritchey mentioned, it is hard to look back in time at something like this unless you have something set up ahead of time. And as he mentioned, change data capture is probably one of the better options. But I like to have a variety of options, so I will add that you can set up a trigger for logging changes or certain types of changes. This is not the best way to track down someone who is deliberately trying to hide their tracks, but at least under certain circumstances it can be better than change data capture for auditing and logging purposes. There's also a good article on [MSSQLTips][1] dealing with tracking this sort of data. [1]: http://www.mssqltips.com/sqlservertip/1468/designing-tables-for-audit-data-in-sql-server/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.