none
Trigger to prevent changing recover model of a database

    Question


  • Hi Team,

    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));
       
        -- 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.
    Thursday, July 04, 2013 8:53 PM

All replies

  • Sorry not tested.. 

    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));
          RAISERROR ('ALTER DATABASE DISABLED!',10, 1)
         ROLLBACK
         -- 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'
    
        
     END     
     GO
     ---trying to change recovery model to 'SIMPLE', we need to prevent this happening!!!!
     ALTER DATABASE testdb SET RECOVERY FULL;
     go

    will this be the ideal solution.. what happens if someone disable the trigger..?? may be you should consider setting appropriate permissions.. 

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Thursday, July 04, 2013 9:36 PM
  • Why do you need this trigger for the DROP DATABASE?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, July 05, 2013 3:39 AM
    Moderator
  • Hi vt,

    We use shared windows ids to login to windows servers and that is the major problem. And the shared ids is part of sysadmin role. I know it is pretty difficult to manage the permissions and Access control but that;s completely a business decission!!

    Coming back I am just looking if this can be solved by a trigger. Atleast I am looking for my self learning.

    How can I achieve it using TSQL code to stop changing recovery models, dropping databases etc....

    Friday, July 05, 2013 4:48 AM
  • Hi Naomi,

    Basically I wanted users to prevent changing recovery models using alter database cmd and prevent DROP DATABASE cmds.

    Friday, July 05, 2013 4:49 AM
  • The piece of code that you originally posted is working fine. not able to clearly understand what happening.

    Satheesh

    Friday, July 05, 2013 5:10 AM
  • >will this be the ideal solution.. what happens if someone disable the trigger..??

    Well yes, one of the big problems with the stealth triggers: they can be dropped silently without any "complaint" what so ever. 

    On proper trigger use:

    http://www.sqlusa.com/bestpractices/trigger-as-fix-it-all/

    >We use shared windows ids to login to windows servers and that is the major problem. And the shared ids is part of sysadmin role.

    A Swiss-army knife trigger will not help you with a bunch of sysadmin-s hitting the system at will.

    Either you limit access to the system by taking away the sysadmin role from those who should not have it or setup a weekly meeting dedicated to discussing problems related to the so many sysadmins' actions during the week.


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Friday, July 05, 2013 6:15 AM
    Moderator
  • Thanks Kalman.

    But one thing I am not understanding, from programmer's perpective if i Look at it, when I am using ROLLBACK statement inside the trigger, why the ALTER DATABASE statement is not getting rollbacked ? Is that ALTER DATABASE statement is automatic-commit as it comes as DDL statement is that so??? The reason why I am asking is, the rollback is undo the process of sending the email via sp_send_dbmail despite I get a message "Mail Queued", then why can't ALTER DATABASE cancelled.

    Plz correct if am wrong.


    • Edited by Manu_vmr Friday, July 05, 2013 7:24 AM add explaination
    Friday, July 05, 2013 7:19 AM