Создаем таблицу под лог
USE [master] GO /****** Объект: Table [dbo].[DDLTriggerAlterLogin] Дата сценария: 02/25/2013 15:22:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DDLTriggerAlterLogin]( [EventType] [nvarchar](100) NULL, [ObjectName] [nvarchar](2000) NULL, [ObjectType] [nvarchar](2000) NULL, [LoginName] [nvarchar](max) NULL, [LoginType] [nvarchar](max) NULL, [SPID] [nvarchar](max) NULL, [PostTime] [datetime] NULL, [host] [nvarchar](128) NULL ) ON [PRIMARY]
Создаём триггер для ALTERDATABASE
/****** Object: DdlTrigger [DDLTriggerAlterDatabase] Script Date: 03/28/2016 10:59:50 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- 1) исключение срабатываний для служебной учетной записи head_offive\mssqlserver, log shipping -- ============================================= CREATE TRIGGER [DDLTriggerAlterDatabase] ON ALL SERVER AFTER ALTER_DATABASE AS DECLARE @eventdata NVARCHAR(MAX) DECLARE @loginname NVARCHAR(MAX) SET @eventdata = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)')) SET @loginname = 'HEAD_OFFICE\MSSQLServer' IF ( @eventdata!=@loginname ) BEGIN INSERT INTO master.dbo.DDLTriggerAlterLogin SELECT EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') 'EventType' , EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(2000)') 'ObjectName' , EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(2000)') 'ObjectType' , EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)') 'LoginName', EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') 'LoginType', EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]','nvarchar(max)') 'SPID', EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]','datetime') 'PostTime', HOST_NAME() AS 'host' END GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ENABLE TRIGGER [DDLTriggerAlterDatabase] ON ALL SERVER GO
Тригер для ALTERLOGIN
CREATE TRIGGER DDLTriggerAlterLogin ON ALL SERVER AFTER ALTER_LOGIN AS INSERT INTO master.dbo.DDLTriggerAlterLogin SELECT EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') 'EventType' , EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(2000)') 'ObjectName' , EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(2000)') 'ObjectType' , EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)') 'LoginName', EVENTDATA().value('(/EVENT_INSTANCE/LoginType)[1]','nvarchar(max)') 'LoginType', EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]','nvarchar(max)') 'SPID', EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]','datetime') 'PostTime', host_name() as 'host' GO CREATE trigger DML_DDLTriggerAlterLogin ON master.dbo.DDLTriggerAlterLogin AFTER INSERT AS EXECUTE msdb.dbo.sp_send_dbmail @profile_name = 'atrans', @recipients = 'email', @subject = 'сработал триггре DDLTriggerAlterLogin', @body = 'select EventType ,ObjectName ,ObjectType ,LoginName ,LoginType ,SPID ,posttime ,host from master.dbo.DDLTriggerAlterLogin'
Тригер для CRATEDATABASE
/****** Object: DdlTrigger [DDLTriggerCreateDatabase] Script Date: 03/28/2016 11:01:45 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [DDLTriggerCreateDatabase] ON ALL SERVER AFTER CREATE_DATABASE AS INSERT INTO master.dbo.DDLTriggerAlterLogin SELECT EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') 'EventType' , EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(2000)') 'ObjectName' , EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(2000)') 'ObjectType' , EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)') 'LoginName', EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') 'LoginType', EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]','nvarchar(max)') 'SPID', EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]','datetime') 'PostTime', HOST_NAME() AS 'host' GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ENABLE TRIGGER [DDLTriggerCreateDatabase] ON ALL SERVER GO
Тригер для DROPDATABASE
/****** Object: DdlTrigger [DDLTriggerDropDatabase] Script Date: 03/28/2016 11:03:21 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [DDLTriggerDropDatabase] ON ALL SERVER AFTER DROP_DATABASE AS INSERT INTO master.dbo.DDLTriggerAlterLogin SELECT EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') 'EventType' , EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(2000)') 'ObjectName' , EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(2000)') 'ObjectType' , EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)') 'LoginName', EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') 'LoginType', EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]','nvarchar(max)') 'SPID', EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]','datetime') 'PostTime', HOST_NAME() AS 'host' GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ENABLE TRIGGER [DDLTriggerDropDatabase] ON ALL SERVER GO
отключение триггера
disable trigger DML_DDLTriggerAlterLogin ON master.dbo.DDLTriggerAlterLogin go enable trigger DML_DDLTriggerAlterLogin ON master.dbo.DDLTriggerAlterLogin
disable trigger DDLTriggerAlterLogin ON ALL SERVER go enable trigger DDLTriggerAlterLogin ON ALL SERVER