sql server 2012 auditing all

Question

Post reply

sql server 2012 auditing all

monilps

SSCommitted

Points: 1686
More actions
January 24, 2013 at 7:15 pm

#273407

Hi,
I want to audit everything in sql server as much as possible..
For example who logged in, what queries are runned and by whom if possible target details also like ipaddress n all if possible.
Similar to application auditing and logging kind of.
Thanks in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Sean Pearce SSCoach Points: 15999 More actions · Answer 1

It sounds like profiler might be the tool for you.

If you are new to profiler I suggest "Mastering SQL Server Profiler" By Brad McGehee.

http://www.sqlservercentral.com/articles/books/65797/

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

Since you're on SQL Server 2012, I would advise avoiding trace events. Instead focus on extended events. They're more light weight and much more programmable. There's no book available on that... yet. Jonathan Kehayias has started working on one. I know because I'm doing the technical edits for it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

Grant Fritchey (1/25/2013)
Since you're on SQL Server 2012, I would advise avoiding trace events. Instead focus on extended events. They're more light weight and much more programmable. There's no book available on that... yet. Jonathan Kehayias has started working on one. I know because I'm doing the technical edits for it.

+1 on using XEvents. Can't wait for the book. Is there an estimated release date yet?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

opc.three (1/26/2013)
Grant Fritchey (1/25/2013)
Since you're on SQL Server 2012, I would advise avoiding trace events. Instead focus on extended events. They're more light weight and much more programmable. There's no book available on that... yet. Jonathan Kehayias has started working on one. I know because I'm doing the technical edits for it.
+1 on using XEvents. Can't wait for the book. Is there an estimated release date yet?

Jonathan is hoping for the summer. Don't hold your breath.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

monilps SSCommitted Points: 1686 More actions · Answer 5

I want to capture and save everything into database.

Thanks.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

monilps (1/29/2013)
I want to capture and save everything into database.
Thanks.

Logging all activity to a table could be disastrous for performance. The recommended way to handle auditing is to write to a file on disk. Later you can load that file to a table somewhere other than your production database and analyze or report on the data from there. Trace and XEvents both provide for writing information to a file.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

dbafromthecold@gmail.com SSChampion Points: 10160 More actions · Answer 7

Hi,

Have a look at Brent Ozar's website, loads of good scripts there (sp_Blitz).

http://www.brentozar.com/

Andrew