Come eseguire il Log delle modifiche alla struttura di un DB....

Molto spesso potrebbe tornare particolarmente utile sapere esattamente quali modifiche sono state effettuate alla struttura di un Database...
Dalla versione SQL Server 2005, grazie ai trigger di database, è possibile ottenerlo senza sforzo... (finalmente direi...)


In un database specifico per i log delle attività creiamo una tabella di log:

create table [dbo].[Log]
(
data datetime,
utente varchar(128),
comando varchar(max),
objname varchar(128),
dbname varchar(128)
)

Nel/Nei Database che desideriamo monitorare creiamo il seguente Trigger :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [Log_Modifiche_Struttura] ON DATABASE FOR
CREATE_TABLE,
ALTER_TABLE,
DROP_TABLE,
CREATE_VIEW,
ALTER_VIEW,
DROP_VIEW,
CREATE_PROCEDURE,
ALTER_PROCEDURE,
DROP_PROCEDURE,
CREATE_FUNCTION,
ALTER_FUNCTION,
DROP_FUNCTION,
CREATE_INDEX,
ALTER_INDEX,
DROP_INDEX,
CREATE_ASSEMBLY,
ALTER_ASSEMBLY,
DROP_ASSEMBLY,
CREATE_TRIGGER,
ALTER_TRIGGER,
DROP_TRIGGER,
CREATE_SCHEMA,
ALTER_SCHEMA,
DROP_SCHEMA
AS

DECLARE @command AS VARCHAR(MAX)--Comando eseguito
DECLARE @login AS VARCHAR(128)--Utente che ha eseguito il comando
DECLARE @db AS VARCHAR(128)--DB su cui ho effettuato l'operazione
DECLARE @objName AS VARCHAR(128)--Oggetto interessato

SELECT @login = SUSER_SNAME()--recupero il nome utente
SELECT @command = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')--recupero il comando di modifica struttura
SELECT @objName =  EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')--Recupero il nome dell'oggetto modificato/creato/eliminato
SELECT @db = DB_NAME()--recupero il nome del database

--Inserisco la modifica nello storico
INSERT INTO NomeDbLog.dbo.[Log]
values
(
getdate(),
@login,
@command,
@objName,
@db
)

GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

DISABLE TRIGGER [Log_Modifiche_Struttura] ON DATABASE
GO
ENABLE TRIGGER [Log_Modifiche_Struttura] ON DATABASE
GO

Ogni operazione di Create, Alter, Drop su tabelle, stored procedure, viste, function, schema, index ed assembly verrà quindi tracciata nella nostra tabella di Log.

Proviamo ora se tutto funziona.

Creo la seguente tabella nel database monitorato :

create table dbo.tabellaprova
(
campo int
)

Effettuo una select sul database di Log :








Troviamo la tabella appena creata.

Siamo quindi in grado di sapere esattamente chi ha fatto cosa e quando sul nostro DB...

Notare l'utilizzo della funzione EventData, che restituisce al chiamante le informazioni in formato Xml. Per ottenere l'informazione specifica del comando T-SQL utilizzo la X-query :

Select @command = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

Spero vi sia utile.

Ciao

Alla prossima