Hvordan logger jeg alle ændringer af stored procedures i SQL Server

Okay, dagens indlæg er nok specifikt rettet mod folk, der arbejder med SQL Server på en arbejdsplads. 

Har du brug for at kunne se, hvem der har ændret fx en stored procedure (SP), kan du aktivere denne Database Trigger på din database. Dette vil logge alle strukturelle ændringer på tabeller, view, triggers, procedurer og funktioner.

Det kræver at du først opretter en database kaldet Utility og i den laver tabellen LogDatabaseChanges med følgende  tabeldesign.

Column Name Date type Allow Nulls
EventDate datetime Unchecked
EventType nvarchar(100) Checked
EventDDL nvarchar(MAX) Checked
DatabaseName nvarchar(255) Checked
SchemaName nvarchar(255) Checked
ObjectName nvarchar(255) Checked
ObjectType nvarchar(255) Checked
HostName nvarchar(255) Checked
IPAddress varchar(32) Checked
ProgramName nvarchar(255) Checked
LoginName nvarchar(255) Checked

 

Herefter kan du oprette triggeren på databaseniveau med følgende script

CREATE TRIGGER [CaptureDatabaseChanges]
    ON DATABASE
    FOR RENAME,
		CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
		CREATE_VIEW, ALTER_VIEW, DROP_VIEW,
		CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,
		CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
		CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @EventData XML = EVENTDATA(), @ip VARCHAR(32);

    SELECT @ip = client_net_address
        FROM sys.dm_exec_connections
        WHERE session_id = @@SPID;

    INSERT Utility.dbo.LogDatabaseChanges
    (
        EventType,
        EventDDL,
        SchemaName,
        ObjectName,
		ObjectType,
        DatabaseName,
        HostName,
        IPAddress,
        ProgramName,
        LoginName
    )
    SELECT
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'), 
        @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'), 
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),
		@EventData.value('(/EVENT_INSTANCE/ObjectType)[1]',  'NVARCHAR(255)'),
        DB_NAME(), HOST_NAME(), @ip, PROGRAM_NAME(), SUSER_SNAME();
END
GO

ENABLE TRIGGER [CaptureDatabaseChanges] ON DATABASE
GO

Fra nu af vil alle ændringer i strukturelle ændringer på tabeller, view, triggers, procedurer og funktioner blive logget i tabellen. Du vil kunne trække en log ud med følgende script

 

select * from Utility.dbo.LogDatabaseChange

Evt. med følgende where-sætning, som henter alle ændringer for en specifik SP (dog wildcard-searched) og sorterer med senest ændringer øverst.

select * from Utility.dbo.LogDatabaseChange
where ObjectName like '%SPName%'
order by EventDate desc

Skriv et svar

Din e-mailadresse vil ikke blive publiceret.