July 4, 2011 at 6:54 am
Hi All
Is it possible to create one DDL trigger at server level for all DDL_EVENTS
CREATE TRIGGER antg ON SERVER
FOR DDL_EVENTS
AS
IF IS_SRVROLEMEMBER('sysadmin') = 0
BEGIN
PRINT 'Only SysAdmins can modify this server'
ROLLBACK TRANSACTION
END
GO
Gives me error
Msg 1098, Level 15, State 1, Procedure antg, Line 8
The specified event type(s) is/are not valid on the specified target object.
Looking in BOL at the event tree, all objects have a server scope so I would of thought declaring at the top level DDL_EVENTS would do this.
Do I need two triggers one for DDL_SERVER_LEVEL_EVENTS and the other for DDL_DATABASE_LEVEL_EVENTS to complete my goal on this?
Thanks
Ant
July 4, 2011 at 6:57 am
DOH missed out the ALL from ALL SERVER, my bad, all is now ok.
July 4, 2011 at 7:34 am
Right on a different note this time, getting execute on sp_send_dbmail denied. Now I dont want to grant all the users the ability to send db mail in MSDB and I tried with EXECUTE AS SA but it didnt like it in the trigger, can anyone help with a secure way to send the mail?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [antg] ON ALL SERVER
FOR DDL_EVENTS
AS
IF IS_SRVROLEMEMBER('sysadmin') = 0
BEGIN
DECLARE @data XML;
DECLARE @schema sysname;
DECLARE @object sysname;
DECLARE @eventType sysname;
DECLARE @Mess sysname;
SET @data = EVENTDATA();
SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')
PRINT 'Only SysAdmins can modify this server'
PRINT 'You'+char(39)+'r statement has been logged and emailed to the DBA team'
ROLLBACK TRANSACTION
INSERT AdventureWorks2008R2.[dbo].[DatabaseLog]
(
[PostTime],
[DatabaseUser],
[Event],
[Schema],
[Object],
[TSQL],
[XmlEvent]
)
VALUES
(
GETDATE(),
CONVERT(sysname, CURRENT_USER),
@eventType,
CONVERT(sysname, @schema),
CONVERT(sysname, @object),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'),
);
SET @Mess =
CONVERT(sysname, current_user) + ' ' +
@eventType + ' ' +
CONVERT(sysname, @schema) + ' ' +
CONVERT(sysname, @object)
exec msdb.dbo.sp_send_dbmail @profile_name = ant, @recipients = 'ant@ant.com', @subject = 'Server level change detected and rolled back', @body = @Mess
END
The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply