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 Thursday, June 12, 2008 4:37 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 10:01 AM
Points: 3,844, Visits: 3,841
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 - by Jeff Moden
Post #516326
Posted Thursday, June 12, 2008 4:56 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
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.




Post #516331
Posted Saturday, June 14, 2008 12:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 28, 2008 9:00 AM
Points: 4, 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 ! :)
Post #517188
Posted Monday, November 24, 2008 2:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 10:08 PM
Points: 110, Visits: 715
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.
Post #607365
Posted Monday, November 24, 2008 9:08 AM


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





Post #607645
Posted Monday, November 24, 2008 9:52 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 10:08 PM
Points: 110, Visits: 715
hi tjay thanks a million for your help.
Post #608033
Posted Wednesday, February 25, 2009 2:02 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 1:01 AM
Points: 588, Visits: 964
Apparently, there are missing SPs in your posted code, one of them was spGetDIRExists. Can you please post them too?

Thanks in advance.

:)
Post #664074
Posted Wednesday, October 14, 2009 11:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, December 1, 2013 5:41 AM
Points: 41, Visits: 200
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.
Post #802960
Posted Monday, October 19, 2009 4:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, December 1, 2013 5:41 AM
Points: 41, Visits: 200
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.
Post #804960
Posted Monday, October 19, 2009 5:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, December 1, 2013 5:41 AM
Points: 41, Visits: 200
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

Post #804979
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse