У меня MS SQL Server 2008 R2
Я создал DB-level trigger для того чтобы регистрировать факты изменения хранимых процедур, функций и тд:
CREATE TABLE [dbo].[System.AuditDDLEvent](
[EventDate] [datetime] NOT NULL,
[EventType] [nvarchar](64) NULL,
[EventDDL] [nvarchar](max) NULL,
[EventXML] [xml] NULL,
[DatabaseName] [nvarchar](255) NULL,
[SchemaName] [nvarchar](255) NULL,
[ObjectName] [nvarchar](255) NULL,
[HostName] [varchar](64) NULL,
[IPAddress] [varchar](32) NULL,
[ProgramName] [nvarchar](255) NULL,
[LoginName] [nvarchar](255) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TRIGGER [DDLTrigger_Audit]
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
CREATE_VIEW, ALTER_VIEW, DROP_VIEW,
CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
CREATE_INDEX, DROP_INDEX, --we do not include ALTER_INDEX to not log the "index rebuild" operations performed by SQL Jobs
CREATE_TYPE, DROP_TYPE,
CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,
CREATE_ASSEMBLY, ALTER_ASSEMBLY, DROP_ASSEMBLY,
RENAME, ALTER_SCHEMA, DROP_SCHEMA
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@EventData XML = EVENTDATA();
DECLARE
@ip VARCHAR(32) =
(
SELECT client_net_address
FROM sys.dm_exec_connections
WHERE session_id = @@SPID
);
INSERT dbo.[System.AuditDDLEvent]
(
EventType,
EventDDL,
EventXML,
DatabaseName,
SchemaName,
ObjectName,
HostName,
IPAddress,
ProgramName,
LoginName
)
SELECT
@EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
@EventData,
DB_NAME(),
@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(255)'),
@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)'),
HOST_NAME(),
@ip,
PROGRAM_NAME(),
SUSER_SNAME();
END
Всё работает правильно. Осталась только проблема обеспечения нужных permissions для этого триггера. Он же использует системные таблицы вроде sys.dm_exec_connections. Конкретный пользователь изменяющий из MS SQL Studio скажем процедуру может и не иметь нужных привилегий для доступа к этим таблицам.
Как сделать так чтобы сработавший при этом триггер [DDLTrigger_Audit] не завалился по причине “the user does not have enough permissions”? использовать “WITH EXECUTE as OWNER” (как я сделал бы например в хранимой процедуре) в таком триггере нельзя.