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
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: 3752 Visits: 6869
Comments posted to this topic are about the item What, When and who? Auditing 101

-Roy
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32437 Visits: 18556
Nice job Roy.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

andre.quitta
andre.quitta
SSC-Enthusiastic
SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)

Group: General Forum Members
Points: 197 Visits: 393
The article was thorough and straighforward.

I find the problem with ChangeTracking and ChangeDataCapture is that there is so much hardcoding going on. That means whenever you add or delete a column, it is another area that needs to be touched. Plus the queries are pretty unwieldly and verbose.

There is a need in my group to send out an email when anything changes among multiple tables. That would be a very long query, and pretty unwieldy.

I have a lot going on with dynamic SQL to do that, but there is a performance hit.
ALZDBA
ALZDBA
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: 12183 Visits: 8924
Great job, Roy.
It's been my pleasure to help out.Smooooth

Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7835 Visits: 25280
Nice article Roy ... now waiting for the follow up article ..

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
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: 3752 Visits: 6869
Thx everyone. It was fun writing this series. I need to finish the SQL Audit in the coming week so that I can send it to you guys for review before I submit. I have already submitted CDC and I am waiting for it to be reviewed.
Andre, What I saw in CT is that it is using the old Tran replication technology to get the job done. For instance, if you add a column you have to do so many things to get the CT to track the changes just like replication of SQL 2000.

-Roy
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8609 Visits: 7660
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.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Andre Guerreiro
Andre Guerreiro
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1345 Visits: 1515
Excelent article, Roy.

I'm thinking that one of the ideal business solutions to use CT is for database synchronization from mobile devices or off-site databases.

To help CT become more "auditable", maybe we could use the syntax:


WITH CHANGE_TRACKING_CONTEXT(@context)
--<INSERT or UPDATE command here>



and send some information about the username or changes inside the @context VARBINARY(128) column. Is there a limitation for using the CT context and would it also become a major pain to maintain like described in the article?

Change Tracking comes with all SQL Server editions so it's a great little feature to have at our disposal. :-)

Best regards,

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
David In BC
David In BC
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 375
I am not sure I agree with,

"Data auditing is the process of doing a profile check and assessing the quality of data, to find how accurate it is. This can be achieved by keeping track of all the data changes."

A couple of points:

- you can't always know that data is correct. If the data is based on mathematical formula, and you have the inputs, you can. But you can't tell that my mobile telephone number is correct, or my date of birth. At least not without external validation. Quality is abstract.

- auditing goes beyond changing data. Tracking the viewing of data, or the printing of data, is very important. Examples can include celebrities who are hospitalized - you want to know if 300 hospital workers are viewing the patients chart (and shouldn't be).

I like this definition better: "[a] systematic examination of information use, resources and flows, with a verification by reference to both people and existing documents, in order to establish the extent to which they are contributing to an organisation’s objectives"


But interesting article.

David
Andre Guerreiro
Andre Guerreiro
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1345 Visits: 1515
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,

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
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