Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

Audit Database Changes in the Real World Expand / Collapse
Author
Message
Posted Wednesday, June 11, 2008 10:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 20, 2013 4:45 AM
Points: 236, Visits: 239
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! :)
Post #515331
Posted Wednesday, June 11, 2008 10:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 4:59 PM
Points: 52, Visits: 155
for a simple solution (that's also Sarbanes-Oxley compatible)
check out:
http://www.nobhillsoft.com/Randolph.aspx



Post #515346
Posted Wednesday, June 11, 2008 11:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 20, 2010 1:30 PM
Points: 2, Visits: 19
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.
Post #515373
Posted Wednesday, June 11, 2008 11:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:28 PM
Points: 11,157, Visits: 12,897
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
Post #515376
Posted Wednesday, June 11, 2008 12:31 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 6, 2010 1:30 PM
Points: 34, Visits: 82
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.
Post #515438
Posted Wednesday, June 11, 2008 1:08 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:28 PM
Points: 11,157, Visits: 12,897
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
Post #515453
Posted Wednesday, June 11, 2008 1:39 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:28 PM
Points: 614, Visits: 441
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.



Post #515470
Posted Wednesday, June 11, 2008 3:38 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:08 PM
Points: 2,607, Visits: 17,906
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
Post #515554
Posted Wednesday, June 11, 2008 6:28 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 24, 2012 5:10 PM
Points: 17, Visits: 844
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?
Post #515599
Posted Thursday, June 12, 2008 2:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 11:17 AM
Points: 6, Visits: 2,560
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.
Post #516264
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse