SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What, When and who? Auditing 101


What, When and who? Auditing 101

Author
Message
tymberwyld
tymberwyld
SSChasing Mays
SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)

Group: General Forum Members
Points: 620 Visits: 275
When SQL 2005 came out, I cringed at the way SQL Notification services worked and basically threw it out the window as an option for change tracking. However, I re-tooled the same concept using SQL Service Broker, and using a simple, generic Trigger, was able to audit ANY table's changes (real-time, with both previous and new values) asynchronously and we were able to audit all databases into one or many audit databases if we wished. Views were created against the meta-data to make "pseudo" tables which resembled the originals, so comparisons could be made. Anyway, it's worked for years and was completely scalable. If we wanted to move the auditing database(s) to another server, it was as easy as "changing the channel" on the service broker, no messy Trigger or Proc changes.



Roy Ernest
Roy Ernest
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3796 Visits: 6869
codebyo (11/28/2011)
Just one small correction: ALLOW_SNAPSHOT_ISOLATION is not needed for Change Tracking but it's the probably the safer way to use it with.

Best regards,

Andre, what you stated here is true. You do NOT need SNAPSHOT ISOLATION on but if you do not, you will need to do some extra work to make sure that all requirements are met. You can read here to get more details regarding how it should be done without snapshot isolation.

EDIT : Regarding CHANGE_TRACKING_CONTEXT, it will work fine if all data changes are done only using applications. Then you have control of it and you get more context of the data change. It is a good info to pass to everyone. Thanks. I missed that in the article.

-Roy
Roy Ernest
Roy Ernest
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3796 Visits: 6869
Thanks David for the compliment.
To agree to the definition of an Audit is difficult. Everyone has their own take on it. Your definition is easier to understand as well. Thanks for sharing it. :-)

-Roy
Roy Ernest
Roy Ernest
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3796 Visits: 6869
Evil Kraig F (11/28/2011)
Roy, an excellent walkthrough on the mechanics of something I've basically avoided. You highlighted its difficulties and its uses quite well.

Thanks for the article.

Thanks Craig. When I look back, I realized that I could have added a bit more in depth information.

-Roy
Zero One
Zero One
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 226
Good article, thanks for the summary.

The biggest issues I've encountered, save for a server side trace, is catching the originating hostname for the incoming connection. To me this is a critical aspect of "who" and without it, it can be difficult in tracking down an individual in the situation where a common SQL login is utilized by multiple individuals (or even a service account implemented on several servers). In this, SQL Server Audit is woefully lacking. Sure we *wish* we could always avoid folks using a SQL login but life is rarely perfect especially when you've inherited someone else's mess to straighten out. To me this is the whole point of auditing is catching non-standard activity but without capturing *correct* hostname you can't easily complete the picture of *who* (all hostname captured information in SQL Audit is the actual host SQL server or blank and not the host of the originating connection).

This of course is Microsoft's issue to resolve, not the author's.
Roy Ernest
Roy Ernest
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3796 Visits: 6869
Thanks Zero one. :-)
Auditing in SQL Server is still in infancy state. It will take couple of more releases to get all the required information for auditing I guess.

-Roy
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87606 Visits: 41118
tymberwyld (11/28/2011)
When SQL 2005 came out, I cringed at the way SQL Notification services worked and basically threw it out the window as an option for change tracking. However, I re-tooled the same concept using SQL Service Broker, and using a simple, generic Trigger, was able to audit ANY table's changes (real-time, with both previous and new values) asynchronously and we were able to audit all databases into one or many audit databases if we wished. Views were created against the meta-data to make "pseudo" tables which resembled the originals, so comparisons could be made. Anyway, it's worked for years and was completely scalable. If we wanted to move the auditing database(s) to another server, it was as easy as "changing the channel" on the service broker, no messy Trigger or Proc changes.


That would make one heck of a nice article. Any chance of you taking that on?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87606 Visits: 41118
Roy Ernest (11/29/2011)
Thanks David for the compliment.
To agree to the definition of an Audit is difficult. Everyone has their own take on it. Your definition is easier to understand as well. Thanks for sharing it. :-)


Heh... I've found that a lot of people define "Audit" as "Keeping historical data so we know who to blame when something goes wrong." :-D

Nicely done on the article. Good order of revelation with nice, simple examples. Well done, Roy.

As a side bar, I'm going to use your article to justify why we don't actually want to use CT when we finally get to 2008 at the new company I'm working at. :-) Of course, if they wait a bit longer, we can jump to 2k12.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Roy Ernest
Roy Ernest
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3796 Visits: 6869
Hey Jeff,

Thanks Jeff. The second part will come out hopefully soon. Are there too many negatives on CT for your company to use CT? :-D

-Roy
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87606 Visits: 41118
Kind of, Roy. It's not actually negatives with CT... I'd be more likely to call it a lack of positives. CT doesn't appear to actually audit, in my feeble ol' mind. If I understand the article correctly, it only keeps one historical row back.

Like I said... good article. The fact that CT doesn't appear to do what I think of as "auditing" has no bearing on the fact that this is an excellent article about what CT does and doesn't do. Thanks for writing it. :-)

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search