none
Audit SQL Server 2000

    Question


  • Hi Everyone,

    I need to audit procedure execution that change a specific table/column.

    E.g.

    CREATE TABLE Test
    (
    Value INT
    )

    When "value" column change from 1 to 2 i need to log the procedure that made the change. For e.g. that procedure:

    CREATE PROCEDURE dbo.AlterTest
    AS
    BEGIN
    UPDATE Test SET Value = 2 WHERE Value = 1
    END

    So... I need to log: "dbo.AlterTest"

    I´m using SQL 2000, with 2005+ its easy...but im not able to think anyway to to that on SQL Server 2000. Can anyone?

    <b>Fabrizzio A. Caputo</b><br/> Certificações:<br/> MCT<br/> MCC<br/> Oracle OCA 11g<br/> MCITP SQL Server 2008 BI<br/> MCITP SQL Server 2008 Implementation and Maintenance<br/> MCITP SQL Server 2008 Developer<br/> ITIL V3 Foundation <br/> Blog Pessoal: <a href="http://fabrizziocaputo.wordpress.com">www.fabrizziocaputo.wordpress.com</a><br/> Email: fabrizzio.antoniaci@gmail.com

    Thursday, July 25, 2013 3:00 PM

Answers

  • Solved.

    SET NOCOUNT ON
    	DECLARE @SQL		VARCHAR(8000)
    	DECLARE @Antigo		INT
    	DECLARE @Novo		INT
    
    	CREATE TABLE #TabelaAuditTemp
    	(
    		EventType	NVARCHAR(4000),
    		Parameters	INT,
    		EventInfo	NVARCHAR(4000)
    	)
    
    	SET @SQL = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')'
    	SET @Antigo = (SELECT TOP 1 cd_Tipo_Juridico FROM Deleted)
    	SET @Novo = (SELECT TOP 1 cd_Tipo_Juridico FROM Inserted)
    
    	INSERT INTO #TabelaAuditTemp EXEC (@SQL)
    
    	IF(@Novo <> @Antigo)
    	BEGIN
    		INSERT INTO TabelaAuditoria(Tipo, Parametros, Info, Antigo, Novo)
    		SELECT [EventType], [Parameters], [EventInfo], @Antigo, @Novo
    		FROM #TabelaAuditTemp


    <b>Fabrizzio A. Caputo</b><br/> Certificações:<br/> MCT<br/> MCC<br/> Oracle OCA 11g<br/> MCITP SQL Server 2008 BI<br/> MCITP SQL Server 2008 Implementation and Maintenance<br/> MCITP SQL Server 2008 Developer<br/> ITIL V3 Foundation <br/> Blog Pessoal: <a href="http://fabrizziocaputo.wordpress.com">www.fabrizziocaputo.wordpress.com</a><br/> Email: fabrizzio.antoniaci@gmail.com

    Thursday, July 25, 2013 4:04 PM

All replies

  • below link will help you

    http://stackoverflow.com/questions/2003605/how-to-find-out-which-package-procedure-is-updating-a-table


    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, July 25, 2013 3:07 PM
  • Solved.

    SET NOCOUNT ON
    	DECLARE @SQL		VARCHAR(8000)
    	DECLARE @Antigo		INT
    	DECLARE @Novo		INT
    
    	CREATE TABLE #TabelaAuditTemp
    	(
    		EventType	NVARCHAR(4000),
    		Parameters	INT,
    		EventInfo	NVARCHAR(4000)
    	)
    
    	SET @SQL = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')'
    	SET @Antigo = (SELECT TOP 1 cd_Tipo_Juridico FROM Deleted)
    	SET @Novo = (SELECT TOP 1 cd_Tipo_Juridico FROM Inserted)
    
    	INSERT INTO #TabelaAuditTemp EXEC (@SQL)
    
    	IF(@Novo <> @Antigo)
    	BEGIN
    		INSERT INTO TabelaAuditoria(Tipo, Parametros, Info, Antigo, Novo)
    		SELECT [EventType], [Parameters], [EventInfo], @Antigo, @Novo
    		FROM #TabelaAuditTemp


    <b>Fabrizzio A. Caputo</b><br/> Certificações:<br/> MCT<br/> MCC<br/> Oracle OCA 11g<br/> MCITP SQL Server 2008 BI<br/> MCITP SQL Server 2008 Implementation and Maintenance<br/> MCITP SQL Server 2008 Developer<br/> ITIL V3 Foundation <br/> Blog Pessoal: <a href="http://fabrizziocaputo.wordpress.com">www.fabrizziocaputo.wordpress.com</a><br/> Email: fabrizzio.antoniaci@gmail.com

    Thursday, July 25, 2013 4:04 PM