Click here to monitor SSC
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
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2494 Visits: 6852
That is true. From what I have seen, we have to use at least two of the new Auditing technology together to get the "Audit" to work.
SQL Audit actually does audit Who and when but not what. :-D

-Roy
Andre Guerreiro
Andre Guerreiro
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1065 Visits: 1514
You can have WHO and WHEN available to you using CT with the CHANGE_TRACKING_CONTEXT method posted in one of my last posts. Storing WHAT changed would be limited by the size of the VARBINARY(128) column so it wouldn't be a good solution.

CT is a lightweight solution available to all editions and it can be quite useful in situations where too much detail isn't needed.

Best regards,

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44962 Visits: 39862
I'll definitely wait for the other articles from Roy but, right now, I'm thinking I'll still with some good ol' fashioned, high performance audit triggers.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
eric.notheisen
eric.notheisen
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 258
This article and the subsequent one are very well done from a technical perspective. I think more emphasis needs to be added as to why auditing needs to be done. Many of the data problems we have are a result of internal people accessing or modifying data for their own benefit. I spent a good many years in the late '80s and early '90s as an internal auditor for Martin Marietta Astronautics Group. You would be amazed at how many people tried to modify database entries when it would affect their bonuses. In the middle 90's working as an internal employee for a consulting company we had a recruiter in one case and a sales rep in another attempt to export all the recruiting contacts and sales contacts. In the mid 2000's I worked as a application and database developer for a small international company. With about 36 people total in the company we had two who individuals who maliciously attempted to modify data. One of the two attempted to export all the company contacts for his own future benefit.

What is so amazing is that these kinds of issues are common across the scope of the business and government world. These actions provide me with a high level of distrust of individuals when it comes to protecting the data in the company I work for. The data in our databases is a vary valuable commodity for our organizations. We, in the IT end of the business, have a fiduciary responsibility to protect it from technical flaws and human flaws. Building in security, and auditing is a part of security, is paramount to our fulfilling our duty to our employers.
Roy Ernest
Roy Ernest
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2494 Visits: 6852
You have a great point Eric. I should have given a bit more importance on why we need to do Auditing. And you have pointed out one of my weakness in all my articles. I usually dont give much importance on what are the uses of a particular functionality. I will keep that in mind next time I write one.

-Roy
jerryschiffelbein 85806
jerryschiffelbein 85806
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 58
Actually, in a perfect world, you wouldn't need auditing -- the data is already perfect.
ALZDBA
ALZDBA
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6970 Visits: 8839
FYI

...Adds a stored procedure for the manual cleanup of the change tracking side table in SQL Server 2014 SP2 ...

sp_flush_CT_internal_table_on_demand [ @TableToClean= ] 'TableName'

Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't 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
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