Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What, when and who? Auditing 101 - Part 2


What, when and who? Auditing 101 - Part 2

Author
Message
Roy Ernest
Roy Ernest
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2750 Visits: 6860
Comments posted to this topic are about the item What, when and who? Auditing 101 - Part 2

-Roy
lotusnotes
lotusnotes
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 217
CDC is fine for tracking DATA but what about WHO changed it? I can't even seem to write a join query to show the changes to the data by who?

Back to audit triggers then, unless anyone can enlighten me.

Thanks



Roy Ernest
Roy Ernest
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2750 Visits: 6860
Who can be done by SQL Audit... I am half way through writing that article. That is the 3rd part of this series.

-Roy
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12994 Visits: 14863
Nice article. One question, you mention that it would be a good idea to have the database in Snapshot Isolation mode, but you don't really give any details as to why? I'd really like to know why I should use snapshot isolation along with CDC.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Roy Ernest
Roy Ernest
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2750 Visits: 6860
I gave a recommendation that for CDC to use Snapshot isolation due to two reason.

1. To make sure that there is no blocking caused when trying to get the LSN.
2. To make sure that you get the right LSN.

On a busy OLTP server, you are going to have high number of data changes and that means that the Max LSN will be changing at a very rapid rate. You want to make sure that the MAX LSN is the same through out the query you are using to retrieve the changes.

But it all depends on how you are retrieving the changes. There fore it is just a recommendation. It is not a must. I hope I was able to answer that question.

-Roy
trevor.pinkney
trevor.pinkney
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 21
Hey Roy,

Looking forward to Part #3. I really think 'Who' changed the data or 'What Process' is critical when it comes to logging.

I wanted to let you know how my company handles auditing 'WHO' in the hopes you may 'speak to it' in your next article.

We use a table similar to "Product". In the stored procedures that change data in this table we force developers to specify a LogUserID and a LogProcessID. The LogUserID represents the person logged into the system that pressed the 'save button' or 'delete button' on the GUI or it may be a system user. The LogProcessID is used to indicate if the change was triggered by a Web Application, A Nightly 'Product Price Update' Job sql server job, a windows service, a web service etc.



PRODUCT TABLE SCHEMA
----------------------------------
ProductID
Description
Price
LogUserID
AppProcessID
DateTimeModified
DateTimeInserted


PRODUCT TABLE SCHEMA IN LOG DATABASE - A trigger inserts into a duplicate table
------------------------------------------
AuditID
Action
ProductID
Description
Price
LogUserID
AppProcessID
DateTimeModified
DateTimeInserted



Anyway - I am really curious about the 'Who' in part #3 and hope you can cover this scenario in your article.
Roy Ernest
Roy Ernest
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2750 Visits: 6860
Hey Trevor,
Half of the article is already done. That part covers the "who". I have to do some work on the article to cover writing to Event log. Once that is done, I will submit it for publication.

Your present idea works when you have a controlled system like that. It just wont store the data if the update or select is done using SSMS. SQL Audit will be able to catch that.

-Roy
trevor.pinkney
trevor.pinkney
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 21
Hey Roy,

You are right. The current method we don't catch SSMS changes. There also isn't a way to "Force" developers to supply a LogUserID. In some cases when we troubleshoot the database will say it was "Roy" that made the change, but really it was a System User because the developer script didn't update the LogUserID column. It gets messy when we delete rows from a table. The first thing we have to do is update the LogUserID for the rows we delete. Then we delete them.

Looking forward to part 3.

-Trevor
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23810 Visits: 18286
Nicely done Roy.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

WayneS
WayneS
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7339 Visits: 10439
Nice article Roy. Thanks!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

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