May 31, 2012 at 10:05 pm
Dear Friends,
I just want to know, if i make any changes on SQl server 2008 like changing the permissions of an user or updating any table etc, where does this all activity get logged. Would like to know the file name or any script or query to get all the changes mad on the server on that particular day.
Thanks in advance
June 1, 2012 at 1:52 am
Certain things are stored in the default trace if its enabled, but by default only 5 20MB files are created and are rolled over once full, so you might have to be quick to find them.
SELECT *
FROM fn_trace_gettable
('C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\LOG\log.trc', default)
GO
Updating data in a table depends on your databases recovery model as to what you can find out. If SIMPLE recovery the update is logged to the TX log then marked as re-useable so tracking is a bit difficult, then with bulk and full recovery models you could purchase a log reader and look at your transaction log backups to see what has changed, but it will not log WHO has changed it.
But by all means anything like this you should be righting your own custom monitoring which suits your needs, things like CDC, audits, extended events etc.
June 1, 2012 at 1:57 am
Thanks for the reply anthony
June 1, 2012 at 8:49 am
You can set up tracing or auditing in SQL Server 2008, but it can be a tremendous amount of data. It depends on what you capture.
If you want data changes, that's a lot of work, and that isn't stored anywhere by default.
June 11, 2012 at 7:22 am
If you need the information for some kind of compliance auditing purposes i'd maybe recommend a third party tool, i've heard idera do one quest or redgate may offer similar thing.
June 12, 2012 at 3:19 pm
Red Gate doesn't have an auditing tool.
I think Idera does, and I believe Lumigent has one.
Disclosure: I work for Red GAte.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply