Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Server level trigger Expand / Collapse
Author
Message
Posted Thursday, July 4, 2013 2:55 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 1:13 PM
Points: 774, Visits: 1,734
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.

Post #1470512
Posted Friday, July 5, 2013 8:06 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 22, 2014 8:46 AM
Points: 146, Visits: 633
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.
Post #1470748
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse