SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Audit Database Changes in the Real World


Audit Database Changes in the Real World

Author
Message
John Rowan
John Rowan
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13630 Visits: 4588
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 Smile.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
tjaybelt
tjaybelt
SSC Eights!
SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)

Group: General Forum Members
Points: 971 Visits: 470
John Rowan (6/12/2008)
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 Smile.


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 SQL PASS Special Interest website for Auditing & Compliance.

I started this SIG last year, and am trying to get a group of people that can help each other out on this topic.



Sergio-729493
Sergio-729493
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 15
Excelent 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 ! Smile
sharon-644450
sharon-644450
Old Hand
Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)

Group: General Forum Members
Points: 372 Visits: 761
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.aspx


and 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.
tjaybelt
tjaybelt
SSC Eights!
SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)

Group: General Forum Members
Points: 971 Visits: 470
sharon (11/24/2008)
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.aspx


and 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.



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.aspx

In 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.



sharon-644450
sharon-644450
Old Hand
Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)

Group: General Forum Members
Points: 372 Visits: 761
hi tjay thanks a million for your help.
optradba
optradba
SSChasing Mays
SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)

Group: General Forum Members
Points: 616 Visits: 1063
Apparently, there are missing SPs in your posted code, one of them was spGetDIRExists. Can you please post them too?

Thanks in advance.

Smile
diopbirama
diopbirama
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 231
Hi,
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.
diopbirama
diopbirama
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 231
Hi,
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.
diopbirama
diopbirama
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 231
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


/*
declare @DirectoryName varchar(255) ,
@DirExists int
set @DirectoryName = 'E:\BACKUPFILES'

exec spGetDirExists @DirectoryName, @DirExists output
print @DirExists
*/

ALTER procedure spGetDirExists
@DirectoryName varchar(250) = null,
@DirExists bit output
as
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 */
--end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
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