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.
You can use the [default trace] 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], or using [extended events] 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. :
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
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] dealing with tracking this sort of data. :