InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 24 May 2012 13:36:25 GMT20http://www.sqlservercentral.com/Forums/Topic514937-427-1.aspxThank you so much for the wellwritten and informative article.You are very generous with yourknowledge. The article was mostcomprehensive.-------------------------------------------------[url=http://www.secure-bytes.com/DATABASE+AUDITING.php]Database Auditing[/url]Wed, 06 Jan 2010 21:43:36 GMTabbala2008http://www.sqlservercentral.com/Forums/Topic514937-427-1.aspxHi,all is ok for me now. But I have some problem with named instance server.The Audit don't work with a named oinstance.Mon, 26 Oct 2009 07:08:55 GMTdiopbiramahttp://www.sqlservercentral.com/Forums/Topic514937-427-1.aspxSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO/* declare @DirectoryName varchar(255) , @DirExists intset @DirectoryName = 'E:\BACKUPFILES' exec spGetDirExists @DirectoryName, @DirExists outputprint @DirExists*/ALTER procedure spGetDirExists @DirectoryName varchar(250) = null, @DirExists bit outputas set NoCount on declare @result int, @ErrorMsg varchar(500) --try --Create temporary table for FileExists create table #fileexists ( doesexist smallint, fileindir smallint, direxist smallint ) -- Insert into the temporary table--print @DirectoryName Insert into #fileexists exec master.dbo.xp_fileexist @DirectoryName if @@Error <> 0 begin set @ErrorMsg = 'An error occurred while calling xp_fileexist, to find a file' Goto ErrorProc end select @DirExists = direxist*fileindir from #fileexists FE drop table #fileexists --finally SuccessProc: return 0 /* success */ --except ErrorProc: if @ErrorMsg is not null and @ErrorMsg <> '' RAISERROR ( @ErrorMsg, 16, 1) return 1 /* failure */ --endGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOMon, 19 Oct 2009 05:50:43 GMTdiopbiramahttp://www.sqlservercentral.com/Forums/Topic514937-427-1.aspxHi,I really want to know how many jobs i must create et what SP i'll put insite.I must remediate some sox control and I want to implement your solution.Mon, 19 Oct 2009 04:52:05 GMTdiopbiramahttp://www.sqlservercentral.com/Forums/Topic514937-427-1.aspxHi,wanted to implement your solution , but I have a lot problemes.Seem that I must to create directory and jobs , but I don't know what kind of these object I must create.Your help for deploying is needed.Wed, 14 Oct 2009 11:18:22 GMTdiopbiramahttp://www.sqlservercentral.com/Forums/Topic514937-427-1.aspxApparently, there are missing SPs in your posted code, one of them was spGetDIRExists. Can you please post them too?Thanks in advance.:)Wed, 25 Feb 2009 02:02:07 GMToptradbahttp://www.sqlservercentral.com/Forums/Topic514937-427-1.aspxhi tjay thanks a million for your help.Mon, 24 Nov 2008 21:52:10 GMTsharon-644450http://www.sqlservercentral.com/Forums/Topic514937-427-1.aspx[quote][b]sharon (11/24/2008)[/b][hr]hi tjay excellent article !!!i was jsut trying to browse thsi site and it give an error.http://sigs.sqlpass.org/SIGs/AuditingCompliance/tabid/135/Default.aspxand also i could not find the code for the jobs - Audit -Database changes / cleeint and Ausit Process files cleint.Please can you tell how do you start the procedue in a job.thanks for your help.[/quote]The PASS website http://www.sqlpass.org has changed recently, and the link to the SIGs has been altered as well. It is now http://www.sqlpass.org/Community/SIGs.aspxIn a job, you can select a variety of execution options, one of which is sql server. any viable sql statement can go here, and be executed. If you simply wanted to select 1, that would work, since its a valid sql statement. Even invalid ones that error can be put in a job, though im not sure of the reason one would want to do that.I would suggest playing with it, and you can see how you want to implement the proc calls from a job yourself. Its fun to play and see the results of job executions, i think.Mon, 24 Nov 2008 09:08:57 GMTtjaybelthttp://www.sqlservercentral.com/Forums/Topic514937-427-1.aspxhi tjay excellent article !!!i was jsut trying to browse thsi site and it give an error.http://sigs.sqlpass.org/SIGs/AuditingCompliance/tabid/135/Default.aspxand also i could not find the code for the jobs - Audit -Database changes / cleeint and Ausit Process files cleint.Please can you tell how do you start the procedue in a job.thanks for your help.Mon, 24 Nov 2008 02:07:03 GMTsharon-644450http://www.sqlservercentral.com/Forums/Topic514937-427-1.aspxExcelent article !Almost the system we have implemented recently with the difference that instead of linked servers the system exports the changes of the "Audit" database (which is installed locally on each monitored server) to text files and these are uploaded to a server, where it's reviewed.Anyway, that's the way a database should monitored. I think it's great you have decided to share your implementation with the rest.Thank you, sir ! :)Sat, 14 Jun 2008 12:13:42 GMTSergio-729493http://www.sqlservercentral.com/Forums/Topic514937-427-1.aspx[quote][b]John Rowan (6/12/2008)[/b][hr]Just wanted to say thanks for sharing. The article was well thought out and well written. The level of detail in your object descriptions was great. I think this will help alot of folks. Again, thanks and good job :).[/quote]thanks a ton for the nice words. I hope it does help. Not to try to draw traffic away from SSC, but i have another site for auditing & compliance. Its just starting up, and points back here for all articles ive published on the topic. For all those out there that want more specific help with this topic and surrounding issues, go and visit the [url=http://sigs.sqlpass.org/SIGs/AuditingCompliance/tabid/135/Default.aspx]SQL PASS Special Interest website for Auditing & Compliance[/url].I started this SIG last year, and am trying to get a group of people that can help each other out on this topic. Thu, 12 Jun 2008 16:56:28 GMTtjaybelthttp://www.sqlservercentral.com/Forums/Topic514937-427-1.aspxJust wanted to say thanks for sharing. The article was well thought out and well written. The level of detail in your object descriptions was great. I think this will help alot of folks. Again, thanks and good job :).Thu, 12 Jun 2008 16:37:13 GMTJohn Rowanhttp://www.sqlservercentral.com/Forums/Topic514937-427-1.aspxIts 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.Thu, 12 Jun 2008 14:33:12 GMTSteve Eblinghttp://www.sqlservercentral.com/Forums/Topic514937-427-1.aspxThank 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?Wed, 11 Jun 2008 18:28:30 GMTDonna Hawley-340938http://www.sqlservercentral.com/Forums/Topic514937-427-1.aspxtjaybelt, 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!ChadWed, 11 Jun 2008 15:38:21 GMT Chad Crawfordhttp://www.sqlservercentral.com/Forums/Topic514937-427-1.aspx[quote][b]Jack Corbett (6/11/2008)[/b][hr][quote][b]don_goodman (6/11/2008)[/b][hr]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.[/quote]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.[/quote]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.Wed, 11 Jun 2008 13:39:16 GMTtjaybelthttp://www.sqlservercentral.com/Forums/Topic514937-427-1.aspx[quote][b]don_goodman (6/11/2008)[/b][hr]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.[/quote]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.Wed, 11 Jun 2008 13:08:31 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic514937-427-1.aspxWhy? 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.Wed, 11 Jun 2008 12:31:27 GMTdon_goodmanhttp://www.sqlservercentral.com/Forums/Topic514937-427-1.aspx[quote][b]Nathan Allan (6/11/2008)[/b][hr]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.[/quote]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_".Wed, 11 Jun 2008 11:09:12 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic514937-427-1.aspxNote 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.Wed, 11 Jun 2008 11:04:28 GMTNathan Allanhttp://www.sqlservercentral.com/Forums/Topic514937-427-1.aspxfor a simple solution (that's also Sarbanes-Oxley compatible)check out:http://www.nobhillsoft.com/Randolph.aspxWed, 11 Jun 2008 10:27:36 GMTyonisionhttp://www.sqlservercentral.com/Forums/Topic514937-427-1.aspx[quote][b]msbasssinger (6/11/2008)[/b][hr]Yes. I grabbed the wrong audit article. My bad, and my apologies.Time to revist the coffee pot. :)[/quote]No worries. Make me one too pls! :)Wed, 11 Jun 2008 10:08:03 GMTPreet_Shttp://www.sqlservercentral.com/Forums/Topic514937-427-1.aspx[quote][b]Anirban Paul (6/11/2008)[/b][hr][quote][b]tonyf (6/11/2008)[/b][hr]Why do all these articles assume that the DBA can be trusted?[/quote]Trust has to be there and I believe most of the DBAs can be trusted.[/quote] 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.Wed, 11 Jun 2008 09:10:52 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic514937-427-1.aspxCode has been added to the article.Wed, 11 Jun 2008 09:03:50 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic514937-427-1.aspxhere is a link i just created on my MSN Live Space. I tested it and it downloaded the file just fine.[url=http://cid-8167087481e7a0c8.skydrive.live.com/self.aspx/SSC/Audit%20Database%20Changes.sql]Sql File[/url]lemme know if this doesnt work.Wed, 11 Jun 2008 09:03:00 GMTtjaybelthttp://www.sqlservercentral.com/Forums/Topic514937-427-1.aspx[quote][b]minijogy (6/11/2008)[/b][hr]Great article!Would you please post the code. It would save us a lot of time if you could post us the code[/quote]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? Wed, 11 Jun 2008 08:58:35 GMTtjaybelthttp://www.sqlservercentral.com/Forums/Topic514937-427-1.aspxGreat article!Would you please post the code. It would save us a lot of time if you could post us the codeWed, 11 Jun 2008 08:55:40 GMTminijogyhttp://www.sqlservercentral.com/Forums/Topic514937-427-1.aspx[quote][b]magarity kerns (6/11/2008)[/b][hr][quote][b]tonyf (6/11/2008)[/b][hr]Why do all these articles assume that the DBA can be trusted?[/quote]Because the DBA knows that independent auditors will eventually catch up with him/her. Maybe not this audit cycle, but someday. Since the DBA's pay and bonuses are a lot less likely to be oriented to benefit cheating, unlike, say, a star sales rep, the DBA is among the least likely to be untrustworthy.At some point there just isn't a next level of watchers to watch the watchers, so don't let the paranoia keep your organization from functioning.[/quote]great point. its what i have felt for a long time. I was so frustrated when the auditor told me that a system like this wouldnt help, cause i could edit the data. he said the same about a spreadsheet report, being that its not an acceptable form of data reporting, because its editable.so i really got his goose one day when he asked for a screenshot (assumed the best form of verification). I went to my virus software, and did a screenshot of the dates of files, then edited them in my image software, putting the date a few hundred years in the future... and it blew his mind that that was no longer a valid and safe form of reporting...it always comes back to trust. If i have something watching me, im more likely to be honest. the more monitoring that occurs, the better the higher ups will feel, and the auditors. and i am free to do my job, which i would do honestly in the first place...Wed, 11 Jun 2008 08:54:06 GMTtjaybelthttp://www.sqlservercentral.com/Forums/Topic514937-427-1.aspx[quote][b]tonyf (6/11/2008)[/b][hr]Why do all these articles assume that the DBA can be trusted?[/quote]Because the DBA knows that independent auditors will eventually catch up with him/her. Maybe not this audit cycle, but someday. Since the DBA's pay and bonuses are a lot less likely to be oriented to benefit cheating, unlike, say, a star sales rep, the DBA is among the least likely to be untrustworthy.At some point there just isn't a next level of watchers to watch the watchers, so don't let the paranoia keep your organization from functioning.Wed, 11 Jun 2008 08:49:09 GMTmagarity kernshttp://www.sqlservercentral.com/Forums/Topic514937-427-1.aspx[quote][b]magarity kerns (6/11/2008)[/b][hr]What a great system! What are the requirements for the monitoring server and how many does yours monitor? Can it be done on the cheap with express or workgroup edition?[/quote]Ive done this on a couple systems, and you are limited by hdd size as the data grows. you will have trace files taking up space. and you will have data files growing as you collect data. You need to keep that in mind as you create a similar system, cause once the file space shrinks, no monitoring occurs... and then you are left with your pants down, so to speak. nothing like having to explain to an auditor why you have a gap in your data collection...since you are running a server side trace, you will need to be able to fire off those procs to accomplish this. im not sure if these are available in express. to be honest, ive never used it. Maybe someone can answer that portion?best suggestion that i can give you is to try it out on whatever system you can get your hands on. even housing this on a prod system is an option, if you have no other sql box to put it on. And as time goes, you'll see if it needs to be moved. moving it is fairly easy to do, as well.Wed, 11 Jun 2008 08:48:42 GMTtjaybelthttp://www.sqlservercentral.com/Forums/Topic514937-427-1.aspx[quote][b]tonyf (6/11/2008)[/b][hr]Why do all these articles assume that the DBA can be trusted?[/quote]Trust has to be there and I believe most of the DBAs can be trusted.Wed, 11 Jun 2008 08:39:32 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic514937-427-1.aspxWhat a great system! What are the requirements for the monitoring server and how many does yours monitor? Can it be done on the cheap with express or workgroup edition?Wed, 11 Jun 2008 08:38:30 GMTmagarity kernshttp://www.sqlservercentral.com/Forums/Topic514937-427-1.aspx[quote][b]tonyf (6/11/2008)[/b][hr]Why do all these articles assume that the DBA can be trusted?[/quote]In this articles case, i am the DBA, and of course i can be trusted...Its a good point though. And a difficult one, when im the one creating the system to monitor myself. If there were more players in this puzzle, i would definitely defer to them to monitor me. But in my experience, it needed to be monitored prior to the other players coming on the field. This is one way. Obviously other policies and procedures can be implemented to assume that distrust and more effectively safegaurd the systems against those untrustworthy DBA'sWed, 11 Jun 2008 08:36:27 GMTtjaybelthttp://www.sqlservercentral.com/Forums/Topic514937-427-1.aspxYes. I grabbed the wrong audit article. My bad, and my apologies.Time to revist the coffee pot. :)Wed, 11 Jun 2008 08:30:09 GMTmsbasssingerhttp://www.sqlservercentral.com/Forums/Topic514937-427-1.aspx[quote][b]msbasssinger (6/11/2008)[/b][hr]I think I have a less complicated approach to audit trails.[/quote]Isn't yours a different type of auditing i.e. changes to data as opposed to what the article is about (changes to database objects) ?Wed, 11 Jun 2008 08:06:09 GMTPreet_Shttp://www.sqlservercentral.com/Forums/Topic514937-427-1.aspx[quote][b]Grant Fritchey (6/11/2008)[/b][hr]Excellent article TJ. Congratulations on a well ordered process.Any chance of posting the code for the procs that start the traces? I'm curious about some of the details of how you did that.[/quote]I second the comment and the question. It would be nice to have the scripts for all the objects.Wed, 11 Jun 2008 07:08:52 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic514937-427-1.aspxA very nice integration of SOX with your change control process. I especially liked step 11 with the yelling and finger pointing. I certainly hope that this is documented for the auditors as part of the official process.Wed, 11 Jun 2008 06:20:45 GMTIan Massihttp://www.sqlservercentral.com/Forums/Topic514937-427-1.aspxExcellent article TJ. Congratulations on a well ordered process.Any chance of posting the code for the procs that start the traces? I'm curious about some of the details of how you did that.Wed, 11 Jun 2008 05:48:29 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic514937-427-1.aspxWhy do all these articles assume that the DBA can be trusted?Wed, 11 Jun 2008 05:27:21 GMTtonyfhttp://www.sqlservercentral.com/Forums/Topic514937-427-1.aspxNice Article,I wish to set up a solution as the one that you explain.I am interested to see the code which you have create to inspire me on SQL Server.Thx.Wed, 11 Jun 2008 04:19:39 GMTBombardier