server trigger

Heisenberg 261 Reputation points
2022-11-17T04:13:13.63+00:00

hi folks,
im trying to create a trigger that will prevent any alter database activity on any database, especially add/delete file or disabling important option like TDE encryption/changing recovery model etc. following is the code. However, when i run alter database statement it throws error as shown in the raiserrror, however alter database changes get executed successfully and database gets altered. Can someone explain what am i doing wrong?

alter trigger [my_trigger]
ON ALL SERVER
FOR ALTER_DATABASE, DROP_DATABASE
AS
BEGIN
DECLARE @alrt xml
DECLARE @Trigger _name sysname, @LoginName sysname, @Tablet sysname, @dbname sysname
SET @alrt = EVENTDATA()
DECLARE @Steinar nvarchar(max)
SELECT @Steinar =cast(@alrt as nvarchar(max));
RAISERROR ('Operation not allowed!',16, 1) with log
ROLLBACK

END
GO

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,279 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. LiHongMSFT-4306 25,571 Reputation points
    2022-11-17T07:57:39.667+00:00

    Hi @Heisenberg
    Try this:

    alter trigger [my_trigger]  
    ON ALL SERVER  
    FOR ALTER_DATABASE, DROP_DATABASE  
    AS  
    BEGIN  
      IF 1=1  
      BEGIN  
        RAISERROR ('Operation not allowed!',16, 1) with log  
        ROLLBACK  
      END  
    END  
    

    Best regards,
    LiHong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  2. Heisenberg 261 Reputation points
    2022-11-17T17:58:53.303+00:00

    hello, thanks for the response, however this is not working as well. i dont think there is any difference in my code and what you have provided.
    Trigger throws error , however database still gets altered. I'm on SQL 2016 SP3

    0 comments No comments

  3. LiHongMSFT-4306 25,571 Reputation points
    2022-11-18T07:19:31.173+00:00

    Hi @Heisenberg
    Here are some points on the documents:
    ALTER DATABASE (Transact-SQL): The ALTER DATABASE statement is not allowed in an explicit or implicit transaction.
    261744-image.png
    ROLLBACK TRANSACTION (Transact-SQL):
    261782-image.png
    This explains why the trigger fired but did not Rollback the transaction.

    Best regards,
    Li Hong

    0 comments No comments