0

I'm using the below database-level trigger for schema-change-auditing.
It works fine, but if I do (drop view - create view), it only stores the sql that created the view, not the one that is dropped.

Say on drop procedure, is there a universal way to save the procedure definition of the old version of the procedure as well ?

IF NOT EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'Schema_Change_Audit_Trigger')
EXECUTE('CREATE TRIGGER [Schema_Change_Audit_Trigger] ON DATABASE FOR CREATE_USER AS BEGIN SELECT 123 AS abc END;'); 
GO 


/*
IF EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'Schema_Change_Audit_Trigger')
DROP TRIGGER Schema_Change_Audit_Trigger ON DATABASE
GO
*/


ALTER TRIGGER Schema_Change_Audit_Trigger 
    ON DATABASE 
FOR 
    -- https://docs.microsoft.com/en-us/sql/relational-databases/triggers/ddl-events?view=sql-server-2017
    -- https://stackoverflow.com/questions/1255947/determine-what-user-created-objects-in-sql-server
    -- https://blog.sqlauthority.com/2015/09/12/sql-server-who-dropped-table-or-database/
    -- https://stackoverflow.com/questions/5299669/how-to-see-query-history-in-sql-server-management-studio
     CREATE_USER, ALTER_USER, DROP_USER 
    ,CREATE_SCHEMA, ALTER_SCHEMA, DROP_SCHEMA 
    ,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_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER 
    ,CREATE_TYPE, DROP_TYPE 
    ,CREATE_INDEX, ALTER_INDEX, DROP_INDEX 
    ,CREATE_QUEUE, ALTER_QUEUE, DROP_QUEUE 
    ,RENAME 
    -- https://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/ 
AS 
BEGIN 
    SET NOCOUNT ON; 

    DECLARE @EventData XML; 
    DECLARE @operation_ip varchar(48); 
    DECLARE @operation_principals AS xml; 
    DECLARE @operation_first_principal AS national character varying(256); 

    SET @EventData = EVENTDATA(); 
    SET @operation_ip = CONVERT(varchar(48), CONNECTIONPROPERTY('client_net_address')); 
    SET @operation_principals = ( 
        SELECT 
            lt.name 
        FROM sys.login_token AS lt 

        INNER JOIN sys.server_principals AS sp 
            ON lt.principal_id = sp.principal_id 

        WHERE (1=1) 
        AND lt.name COLLATE Latin1_General_CI_AS NOT IN (SELECT name FROM sys.database_principals WHERE type = 'R'  AND name IS NOT NULL) 
        AND lt.name COLLATE Latin1_General_CI_AS NOT IN (SELECT name FROM sys.server_principals WHERE type = 'R' AND name IS NOT NULL ) 
        AND lt.name IS NOT NULL 

        GROUP BY 
            lt.name 

        ORDER BY 
            name 

        FOR XML PATH(''), ROOT ('names') 
    ); 

    SET @operation_first_principal = ( 
        SELECT TOP 1 
            p.c.value('.', 'nvarchar(256)') AS names 
        FROM @operation_principals.nodes('//name') AS p(c) 
    ); 


    IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'schema_audit' AND TABLE_NAME = 'ddl_events') 
        RETURN; 

    INSERT schema_audit.ddl_events 
    ( 
         audit_event_type 
        ,audit_schema_name 
        ,audit_object_name 
        ,audit_sql_command 
        ,audit_event_xml 

        ,audit_ip_address 
        ,audit_host_name 
        ,audit_app_name 

        ,audit_principal 
        ,audit_principals 

        ,audit_user 
        ,audit_current_user 
        ,audit_session_user 
        ,audit_system_user 
        ,audit_user_name -- same as user 
        ,audit_suser_name 
        ,audit_suser_sname 
        ,audit_original_login 

        ,audit_is_sysadmin 
        ,audit_is_db_owner 
        ,audit_is_ddl_admin 
        ,audit_is_db_datareader 

        ,audit_machine_name 
        ,audit_instance_name 
        ,audit_server_name 
        ,audit_netbios_name 
        ,audit_database_name 

        ,audit_net_transport 
        ,audit_protocol_type 
        ,audit_auth_scheme 
        ,audit_local_net_address 
        ,audit_local_tcp_port 
        ,audit_client_net_address 
        ,audit_physical_net_transport 
    ) 
    SELECT 
         -- Did what ? 
         @EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)') AS audit_event_type 
        ,@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(256)') AS audit_schema_name 
        ,@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(256)') AS audit_object_name 
        ,@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)') AS audit_sql_command 
        ,@EventData AS audit_event_xml 

         -- Where from ? 
        ,@operation_ip AS audit_ip_address 
        ,HOST_NAME() AS audit_host_name 
        ,APP_NAME() AS audit_app_name 

         -- Who did it ? 
        ,@operation_first_principal AS audit_principal 
        ,@operation_principals AS audit_principals 

        ,CAST(USER AS nvarchar(256)) AS audit_user 
        ,CAST(CURRENT_USER AS nvarchar(256)) AS audit_current_user 
        ,CAST(SESSION_USER AS nvarchar(256)) AS audit_session_user 
        ,CAST(SYSTEM_USER AS nvarchar(256)) AS audit_system_user 
        ,CAST(USER_NAME() AS nvarchar(256)) AS audit_user_name -- same as user 
        ,CAST(SUSER_NAME() AS nvarchar(256)) AS audit_suser_name 
        ,CAST(SUSER_SNAME() AS nvarchar(256)) AS audit_suser_sname 
        ,CAST(ORIGINAL_LOGIN() AS nvarchar(256)) AS audit_original_login 

         -- What rights did said person have ? 
        ,IS_SRVROLEMEMBER('sysadmin') AS audit_is_sysadmin 
        ,IS_MEMBER('db_owner') AS audit_is_db_owner 
        ,IS_MEMBER('db_ddladmin') AS audit_is_ddl_admin 
        ,IS_MEMBER('db_datareader') AS audit_is_db_datareader 

         -- On which server was this done ? 
        ,CAST(SERVERPROPERTY(N'MachineName') AS nvarchar(255)) AS audit_machine_name 
        ,CAST(SERVERPROPERTY(N'InstanceName') AS nvarchar(255)) AS audit_instance_name 
        ,CAST(SERVERPROPERTY(N'ServerName') AS nvarchar(255)) AS audit_server_name 
        ,CAST(SERVERPROPERTY(N'ComputerNamePhysicalNetBIOS') AS nvarchar(255)) AS audit_netbios_name 
        ,DB_NAME() AS audit_database_name 

         -- Connection information 
        ,CAST(ConnectionProperty('net_transport') AS nvarchar(255)) AS audit_net_transport 
        ,CAST(ConnectionProperty('protocol_type') AS nvarchar(255)) AS audit_protocol_type 
        ,CAST(ConnectionProperty('auth_scheme') AS nvarchar(255)) AS audit_auth_scheme 
        ,CAST(ConnectionProperty('local_net_address') AS nvarchar(255)) AS audit_local_net_address 
        ,CAST(ConnectionProperty('local_tcp_port') AS nvarchar(255)) AS audit_local_tcp_port 
        ,CAST(ConnectionProperty('client_net_address') AS nvarchar(255)) AS audit_client_net_address 
        ,CAST(ConnectionProperty('physical_net_transport') AS nvarchar(255)) AS audit_physical_net_transport 
    WHERE (1=1) 
    -- every morning, index compression of all indices at 05:15
    -- we don't want to to log that
    AND 
    ( 
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)')  IS NULL 
        OR 
        @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)') IS NULL 
        OR 
        NOT 
        ( 
            @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)')  = 'ALTER_INDEX' 
            AND 
            ( 
                @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)') LIKE '%REORGANIZE%' 
                AND 
                @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)') LIKE '%LOB_COMPACTION%' 
            ) 
        ) 
    ); 

END 


GO 

Log table:

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'schema_audit' )
EXECUTE('CREATE SCHEMA schema_audit; ');
GO

-- IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'schema_audit' ) EXECUTE('DROP TABLE schema_audit.ddl_events; ');

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'schema_audit' AND TABLE_NAME = 'ddl_events' )
BEGIN
    CREATE TABLE schema_audit.ddl_events 
    ( 
         audit_uid uniqueidentifier NOT NULL DEFAULT NEWID() 
        ,audit_event_date datetime NOT NULL DEFAULT CURRENT_TIMESTAMP 
        ,audit_event_type national character varying(64) 
        ,audit_schema_name national character varying(256) 
        ,audit_object_name national character varying(256) 
        ,audit_sql_command national character varying(MAX) 
        ,audit_event_xml xml 

        ,audit_ip_address character varying(48) 
        ,audit_host_name national character varying(256) 
        ,audit_app_name national character varying(256) 

        ,audit_principal national character varying(256) 
        ,audit_principals xml 

        ,audit_user national character varying(256) 
        ,audit_current_user national character varying(256) 
        ,audit_session_user national character varying(256) 
        ,audit_system_user national character varying(256) 
        ,audit_user_name national character varying(256) -- same user 
        ,audit_suser_name national character varying(256) 
        ,audit_suser_sname national character varying(256) 
        ,audit_original_login national character varying(256) 

        ,audit_is_sysadmin int 
        ,audit_is_db_owner int 
        ,audit_is_ddl_admin int 
        ,audit_is_db_datareader int 

        ,audit_machine_name national character varying(256) 
        ,audit_instance_name national character varying(256) 
        ,audit_server_name national character varying(256) 
        ,audit_netbios_name national character varying(256) 
        ,audit_database_name national character varying(256) 

        ,audit_net_transport national character varying(256) 
        ,audit_protocol_type national character varying(256) 
        ,audit_auth_scheme national character varying(256) 
        ,audit_local_net_address national character varying(256) 
        ,audit_local_tcp_port national character varying(256) 
        ,audit_client_net_address national character varying(256) 
        ,audit_physical_net_transport national character varying(256) 

        ,CONSTRAINT PK_ddl_events PRIMARY KEY( audit_uid ) 
    ); 
END 
0

It's best to add the existing database or the routines only to source control to manage.

Store changes of routines definition may cause database size growing fast, it hences impacts backup\restore from my view.

Looking forward to seeing another guru's ideas. Cheers

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.