Logging User Access and changes to the Database, SQL Server 2008

Question

Greetings,

My managers want to know if there is a mechanism in SQL Server 2008 that will log who connects to specific databases and then also log changes made to that database.  We tried the trigger thing, but that seems to break when something happens and then it doesn't do anything else.

Is there something built in that I can't find or a seperate app?

Thanks in advance for your assistance.  

And Please do not post something and mark it as the answer unless I reply that it resolves the issue.  I get tired of point hogs buttering their own plates..if you know what I mean.

Thanks again,

Answer 1

Hi,

It sounds like a case for SQL Audit http://msdn.microsoft.com/en-us/library/cc280386.aspx

Otherwise, out of the box, the only way to do it would be profiler, but there would be a heavy performance penalty if you planned to run it all the time.

---

Peter Carter http://sqlserverdownanddirty.blogspot.com/

Answer 2

Thanks Pete,

I will give that a try and see how it does.   I think that is what they tried before I worked here, but I don't think it was implemented correctly.  I have a test SQL Server now and will give it a go on it.

I had heard about profiler and it's system hungryness (is that a word?), our production server doesn't need that..lol

Thanks again,

Mike

Answer 3

OK well we played around with SQL Audit and it still didn't do what we wanted.   What the SQl managers are looking for is a way to monitor and log when someone runs a query against a specific SQL Database.

Is that possible via Audit or even a third party vendor?

Thanks again,

Mike