Audit Database Changes in the Real World

Question

Audit Database Changes in the Real World

Viewing 12 posts - 31 through 41 (of 41 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

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 :).

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

tjaybelt SSCrazy Points: 2604 More actions · Answer 2

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 :).

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 SSC Journeyman Points: 90 More actions · Answer 3

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 ! 🙂

sharon-644450 Default port Points: 1442 More actions · Answer 4

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 SSCrazy Points: 2604 More actions · Answer 5

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 Default port Points: 1442 More actions · Answer 6

sharon-644450

Default port

Points: 1442

November 24, 2008 at 9:52 pm

#902878

hi tjay thanks a million for your help.

optradba Right there with Babe Points: 732 More actions · Answer 7

Apparently, there are missing SPs in your posted code, one of them was spGetDIRExists. Can you please post them too?

Thanks in advance.

🙂

diopbirama SSC Enthusiast Points: 129 More actions · Answer 8

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 SSC Enthusiast Points: 129 More actions · Answer 9

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 SSC Enthusiast Points: 129 More actions · Answer 10

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

diopbirama SSC Enthusiast Points: 129 More actions · Answer 11

Hi,

all is ok for me now. But I have some problem with named instance server.

The Audit don't work with a named oinstance.

abbala2008 SSC Enthusiast Points: 100 More actions · Answer 12

Thank you so much for the well

written and informative article.

You are very generous with your

knowledge. The article was most

comprehensive.

-------------------------------------------------

Database Auditing