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
Best Answer
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