Its a common question, that How to trace changes made in database objects. Like,
Who has drooped my table ?
Who made changes in my view?
Who made changes in store procedure/Function?
You can solve this problem by creating a DDL (Data Definition Language) i.e. Create, Update, Drop trigger, to trace changes made in a database.
--==============CREATE table to store changes
CREATE TABLE [dbo].[ChangeLog](
[LogId] [int] IDENTITY(1,1) NOT NULL,
[DatabaseName] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EventType] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectName] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectType] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SqlCommand] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EventDate] [datetime] NOT NULL CONSTRAINT [DF_EventsLog_EventDate] DEFAULT (getdate()),
[LoginName] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
go
--============= CREATE DATABASE TRIGGER TO INSERT CHANGES INTO dbo.changelog TABLE ===========
CREATE trigger backup_objects
on database
for create_procedure, alter_procedure, drop_procedure,
create_table, alter_table, drop_table,
create_function, alter_function, drop_function
as
set nocount on
declare @data xml
DECLARE @client_ip VARCHAR(15)
set @data = EVENTDATA()
SELECT @client_ip = client_net_address
FROM sys.dm_exec_connections
WHERE session_id =@data.value('(/EVENT_INSTANCE/SPID)[1]', 'varchar(256)')
insert into YOURDATABASE.dbo.changelog(databasename, eventtype,
objectname, objecttype, sqlcommand, loginname)
values(
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),
@data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'varchar(256)') +'.'+
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')+'('+@client_ip+')'
)
Announcing SQL Server 2025
1 day ago
what about versioning?
ReplyDeleteChange the "ALTER" to "Create" in trigger.
ReplyDeleteI cannot get this to work, since the trigger does not have the rights to query sys.dm_exec_connections. What now?
ReplyDeleteCan you audit even the foreign key constraints or just a basic (create table, drop table & alter table)?
ReplyDelete