HOW TO TRACK STORED PROCEUDRE, TABLE, FUNCTION ALTERATION IN SQL SERVER ?
CREATE TABLE [dbo].[AdministratorLog](
[databasename] [varchar](256) NULL,
[eventtype] [varchar](50) NULL,
[objectname] [varchar](256) NULL,
[objecttype] [varchar](25) NULL,
[sqlcommand] [varchar](max) NULL,
[loginname] [varchar](256) NULL,
[createdon] [datetime] NULL
)
GO
CREATE TRIGGER [Admin_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
SET @data = EVENTDATA()
INSERT INTO dbo.AdministratorLog(databasename, eventtype,objectname, objecttype, sqlcommand, loginname,createdon)
VALUES(
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'), -- value is case-sensitive
@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)'),GETDATE()
)
GO
Thanks to Deeraj & dotnetfunda
No comments:
Post a Comment