Tag: log

database_sql_watch_TSQL

This is to catch and log ddl changes on multiple databases on the same server.

Step 1 : Create the new schema audit

-----------------------------------------------------------------------------------------------------------------------
--  Use tab stop value = 2 et indent spaces = 2
-----------------------------------------------------------------------------------------------------------------------
--  Name				: database_sql_watch
--	Author			:	Thierry Bertin
--							:	www.abcdata.ca
--	Date				:	2018-04-10
--	Comment			:	To catch and log ddl change on multiple databases
-----------------------------------------------------------------------------------------------------------------------
--  Change Log	:	Date				-	Author					-	Description
--                -----------------------------------------------------------------------------------------------------
--								2018-04-10	-	Thierry Bertin 	-	Create Schema, Table, and database trigger for sql_watch
-----------------------------------------------------------------------------------------------------------------------

-- To be created on each database to be watched
create schema Audit
							authorization dbo;
go

Step 2 : Create the new audit table on the database that support audit activity

-----------------------------------------------------------------------------------------------------------------------
--  Use tab stop value = 2 et indent spaces = 2
-----------------------------------------------------------------------------------------------------------------------
--  Name				: database_sql_watch
--	Author			:	Thierry Bertin
--							:	www.abcdata.ca
--	Date				:	2018-04-10
--	Comment			:	To catch and log ddl change on multiple databases
-----------------------------------------------------------------------------------------------------------------------
--  Change Log	:	Date				-	Author					-	Description
--                -----------------------------------------------------------------------------------------------------
--								2018-04-10	-	Thierry Bertin 	-	Create Schema, Table, and database trigger for sql_watch
-----------------------------------------------------------------------------------------------------------------------

-- To be created on the database that support audit activity
create table Audit.SQL_Change_Log
(
		SQL_Change_Log_Id 		integer 						identity (1 , 1) 				not null
	,	Database_Name 				varchar (256) 															not null
	,	Event_Type 						varchar	(50) 																not null
	,	Object_Name 					varchar	(256) 															not null
	,	Object_Type 					varchar	(25) 																not null
	,	Sql_Command 					varchar	(7000) 															not null
	,	Event_Date 						datetime 																		not null 	default (getdate ())
	,	Login_Name 						varchar	(256) 															not null
)
on [primary];
go

Step 3 : Create the database trigger on each database to be watched

-----------------------------------------------------------------------------------------------------------------------
--  Use tab stop value = 2 et indent spaces = 2
-----------------------------------------------------------------------------------------------------------------------
--  Name				: database_sql_watch
--	Author			:	Thierry Bertin
--							:	www.abcdata.ca
--	Date				:	2018-04-10
--	Comment			:	To catch and log ddl change on multiple databases
-----------------------------------------------------------------------------------------------------------------------
--  Change Log	:	Date				-	Author					-	Description
--                -----------------------------------------------------------------------------------------------------
--								2018-04-10	-	Thierry Bertin 	-	Create Schema, Table, and database trigger for sql_watch
-----------------------------------------------------------------------------------------------------------------------

-- To be created on each database to be watched
create trigger catch_sql_object_change                                    
on database                                                      
for 
	create_procedure
, alter_procedure
, drop_procedure
, create_table
, alter_table
, drop_table
, create_function
, alter_function
, drop_function                   
, create_view
, alter_view
, drop_view            
as                                                               
begin                                                                 
	set nocount on;
	                                                
  declare @data xml;                                                

	set @data = eventdata ()                                          
                                                                 
	insert 
	into 							[database name].Audit.SQL_Change_Log	
										(Database_Name , Event_Type , Object_Name , Object_Type , Sql_Command , Login_Name)               
	values						(	
												@data.value	('(/EVENT_INSTANCE/DatabaseName)	[1]' 	, 'varchar	(256)')
											, @data.value	('(/EVENT_INSTANCE/EventType)			[1]' 	, 'varchar	(50)')
											, @data.value ('(/EVENT_INSTANCE/ObjectName)		[1]'	, 'varchar	(256)')
											,	@data.value	('(/EVENT_INSTANCE/ObjectType)		[1]'	, 'varchar	(25)')
											,	@data.value	('(/EVENT_INSTANCE/TSQLCommand)		[1]'	, 'varchar	(7000)')
											,	@data.value	('(/EVENT_INSTANCE/LoginName)			[1]'	, 'varchar	(256)')    
										);
	set nocount off;
end;
go

enable trigger catch_sql_object_change on database;
go
-----------------------------------------------------------------------------------------------------------------------