Audit Database Changes in the Real World

Question

Post reply

Audit Database Changes in the Real World

Viewing 15 posts - 16 through 30 (of 41 total)

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

minijogy Newbie Points: 7 More actions · Answer 1

Great article!

Would you please post the code. It would save us a lot of time if you could post us the code

tjaybelt SSCrazy Points: 2604 More actions · Answer 2

minijogy (6/11/2008)
Great article!
Would you please post the code. It would save us a lot of time if you could post us the code

im not sure why the code isnt out there. Ive emailed SSC to see about adding it back in.

does anyone have a location i can upload it too, and provide a link for others to grab it from?

tjaybelt SSCrazy Points: 2604 More actions · Answer 3

here is a link i just created on my MSN Live Space. I tested it and it downloaded the file just fine.

Sql File

lemme know if this doesnt work.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 4

Steve Jones - SSC Editor

SSC Guru

Points: 736212

June 11, 2008 at 9:03 am

#827322

Code has been added to the article.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

Anirban Paul (6/11/2008)
tonyf (6/11/2008)
Why do all these articles assume that the DBA can be trusted?
Trust has to be there and I believe most of the DBAs can be trusted.

At some point you have to have trust in order to get any work done. Steve Jones has mentioned bonding for DBAs several times in his editorials just for this reason. This is also why we are usually subject to background checks and drug tests.

Jack Corbett
Consultant - Straight Path Solutions
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

Preet_S SSCrazy Points: 2184 More actions · Answer 6

msbasssinger (6/11/2008)
Yes. I grabbed the wrong audit article. My bad, and my apologies.
Time to revist the coffee pot. 🙂

No worries. Make me one too pls! 🙂

yonision Ten Centuries Points: 1033 More actions · Answer 7

for a simple solution (that's also Sarbanes-Oxley compatible)

check out:

http://www.nobhillsoft.com/Randolph.aspx

Nathan Allan Newbie Points: 6 More actions · Answer 8

Note that naming stored procedures starting with "sp_" causes SQL Server to search master for the procedure first, causing cache misses and more expensive name resolution.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 9

Nathan Allan (6/11/2008)
Note that naming stored procedures starting with "sp_" causes SQL Server to search master for the procedure first, causing cache misses and more expensive name resolution.

You should note that the author states that the procedures were originally placed in master and then moved to another database and that the author admits to "lazily" leaving the names "sp_".

Jack Corbett
Consultant - Straight Path Solutions
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

don_goodman Say Hey Kid Points: 670 More actions · Answer 10

Why? You have C2 security enablement that does all the work for you to generate traces of everything. You have :fn_trace_gettable to read the traces so you don't have to leave QA or SMS.

Yours was pointless work.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 11

don_goodman (6/11/2008)
Why? You have C2 security enablement that does all the work for you to generate traces of everything. You have :fn_trace_gettable to read the traces so you don't have to leave QA or SMS.
Yours was pointless work.

I think stating that someone's work is pointless is being a little harsh. Especially since C2 auditing is at the server level and audits everything on the server to a pre-determined location. The article outlines how to audit specific events in specific databases to a user-defined location. Why would I want record and then have to sift through every action on the server when all I really need to know about is activity in one database?

If you think C2 auditing is a better solution for this specific purpose outlined in the article then take the time to offer something constructive instead of denigrating the work done by others.

Jack Corbett
Consultant - Straight Path Solutions
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

tjaybelt SSCrazy Points: 2604 More actions · Answer 12

Jack Corbett (6/11/2008)
don_goodman (6/11/2008)
Why? You have C2 security enablement that does all the work for you to generate traces of everything. You have :fn_trace_gettable to read the traces so you don't have to leave QA or SMS.
Yours was pointless work.
I think stating that someone's work is pointless is being a little harsh. Especially since C2 auditing is at the server level and audits everything on the server to a pre-determined location. The article outlines how to audit specific events in specific databases to a user-defined location. Why would I want record and then have to sift through every action on the server when all I really need to know about is activity in one database?
If you think C2 auditing is a better solution for this specific purpose outlined in the article then take the time to offer something constructive instead of denigrating the work done by others.

Thanks for the support Jack. I agree with what you said. I initially investigated C2 auditing as a solution, but found the level of auditing it was to perform, and some of the drawbacks associated with it too costly for what i wanted. So, this solution is a leaner version of auditing for specific events. With C2 auditing, when its enabled, if you turn off that trace, the default result is the sql server shuts down. The ability to bring down the prod server by stopping a trace was a high risk, with multiple people able to get in and inadvertantly do this. With the system i described, when its disabled, the prod server continues marching along just fine.

In the end, this is one way of solving a specific problem. There are always multiple ways to do tasks, and i hope that people will see some ideas from this that will help them out in their Auditing and Compliance needs.

Chad Crawford SSChampion Points: 11610 More actions · Answer 13

tjaybelt, this is way cool, thanks so much for being willing to share.

Do you know offhand the differences between using this method and replacing the trace files with a DDL trigger? I can think of two - interoperability with SQL 2K, and there are some events that you can't catch with a DDL trigger. I was looking at using DDL triggers to do some auditing, and if there is not too much functionality difference may replace the trace portion with a DDL trigger and take advantage of the central administration process you've built.

Thanks!

Chad

Donna Hawley-340938 SSC Enthusiast Points: 103 More actions · Answer 14

Thank you for your interesting article. I was working through it this afternoon, but I am missing a procedure "spGetDirExists". I that something that should have been included?

Steve Ebling Newbie Points: 6 More actions · Answer 15

Its really the same as the spGetFileExists. see the doc for the xp_fileexists stored procedure that it calls. One of the columns in the result set indicates if the 'file' is a directory.