Server level trigger

• Oracle_91

  SSChampion

  Points: 14548

  More actions

  July 4, 2013 at 2:55 pm

  #279491

  Hi,

  I wanted to a create server level trigger which prevents change of recovery model

  I got some resources from internet and here is the code.

  There are couple of problems with this code.

  1. When I issue ALTER DATABASE COMMAND , it says Mail queued and because of the below ROLLBACK statement it is getting rollbacked and I dont receive any kind of email.

  2. Other thing is that, I am able to see the RAISERROR() msg in management studio, but the ALTER DATABASE statement somehow getting auto-commit which allows the recovery model to be changed.

  Can anybody help me in acheiving this task. what necessary changes/logic should i need to incorporate in the below code to make the mail functionality as well prevent ALTER DATABASE statement getting executed.

  Other alternative is Policy Based Management but I wanted to implement this using trigger.

  Thanks in Advance.

  Use master

  go

  create database testdb

  go

  ALTER DATABASE testdb SET RECOVERY FULL;

  go

  create trigger [usp_Restrict_RecoveryModel_Changes]

  ON ALL SERVER

  FOR ALTER_DATABASE, DROP_DATABASE

  AS

  BEGIN

  DECLARE @data xml

  DECLARE @trigger_name sysname, @LoginName sysname, @UserName sysname, @dbname sysname

  SET @data = EVENTDATA()

  DECLARE @STR nvarchar(max)

  SELECT @STR=cast(@data as nvarchar(max));

  /*

  <EVENT_INSTANCE>

  <EventType>ALTER_DATABASE</EventType>

  <PostTime>2007-01-12T20:05:27.527</PostTime>

  <SPID>65</SPID>

  <ServerName>NAME</ServerName>

  <LoginName>sa</LoginName>

  <DatabaseName>TestSecurity</DatabaseName>

  <TSQLCommand>

  <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />

  <CommandText>ALTER DATABASE [TestSecurity] SET RECOVERY SIMPLE WITH NO_WAIT

  </CommandText>

  </TSQLCommand>

  </EVENT_INSTANCE>

  */

  -- send email to self before you will raise error

  EXEC msdb.dbo.sp_send_dbmail

  @recipients='test@gmail.com',

  @subject = '!!!****Attempt to alter database***!!!',

  @body = @STR,

  @body_format = 'HTML',

  @profile_name ='Test profile'

  RAISERROR ('ALTER DATABASE DISABLED!',10, 1)

  ROLLBACK

  END

  GO

  ---trying to change recovery model to 'SIMPLE', we need to prevent this happening!!!!

  ALTER DATABASE testdb SET RECOVERY FULL;

  go

  Thanks in Advance.

• Matthew Darwin

  SSCertifiable

  Points: 5513

  More actions

  July 5, 2013 at 8:06 am

  #1630083

  I think you need to move the ROLLBACK to before the mail send and raiserror, this will ensure that the ALTER DATABASE statement is rolled back but then allow the mail to be sent.

  Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]