How to deny viewing definition of server level trigger?

Question

I have several server level triggers, that I want users to deny viewing definition of. I control server level permissions, users aren't sysadmins etc.

I've tried 2 options:

DENY VIEW ANY DEFINITION to public

it prevents users from even seeing that those triggers exist (and who knows what else), so I don't want to go with that option

CREATE SERVER TRIGGER ... WITH ENCRYPTION

It's an option, but it says that those triggers won't be replicated, and I need to have those triggers replicated (although I can recreate them on replicas, if necessary).

Could you advice if there any other options?

Ideally it would be nice to have something like

DENY VIEW DEFINITION ON OBJECT::trigger_name TO PUBLIC

but I can't make it work for server level trigger

Answer 1

in a test server I create the following server side trigger for an experiment.

this trigger prevents users from dropping logins.

    --set parseonly on
    --set parseonly off

    --=======================================================================================
    -- create a trigger that prevents dropping of logins
    --=======================================================================================


    USE [master]
    GO

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    create trigger [no_dropped_logins] 
    on all server 
    for drop_login 
    as 
      insert into ##LOGIN_WATCH 
        select r.*, s.login_name, s.host_name,  
        EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'nvarchar(max)') AS 'CmdText'
         from sys.dm_exec_requests r 
           inner join sys.dm_exec_sessions s 
           on r.session_id = s.session_id 
           where r.session_id = @@SPID
    GO

    ENABLE TRIGGER [no_dropped_logins] ON ALL SERVER
    GO

second step I create a login in order to use it in the experiment later on.

--==============================================================
-- create a login in order to drop it and call the trigger
--==============================================================


USE [master]
GO

--DROP LOGIN [Radhe]
--GO

CREATE LOGIN [Radhe] WITH PASSWORD=N'Shy4m', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

ALTER LOGIN [Radhe] enable
GO

as a third step I connect as Radhe and check what I can see:

as you can see on the picture above - you cannot see the server side trigger.

another test: running a query as login Radhe I cannot see the server side trigger.

execute as login='radhe'

SELECT
       *
FROM sys.server_triggers

However, when I run the same query as myself:

NOW I grant the VIEW ANY DEFINITION and see what happens:

GRANT VIEW ANY DEFINITION TO [RADHE]
GO

now Radhe can see the server side trigger as per the picture above.

check this out:

running this script:

--==================================================================================
--GRANT VIEW ANY DEFINITION AND RADHE CAN SEE THE SERVER SIDE TRIGGER
--==================================================================================
GRANT VIEW ANY DEFINITION TO [RADHE]
GO
execute as login='radhe'

SELECT * FROM sys.server_triggers
GO
revert
GO
--==================================================================================
--REVOKE VIEW ANY DEFINITION AND RADHE CAN NO LONGER SEE THE SERVER SIDE TRIGGER
--==================================================================================
REVOKE VIEW ANY DEFINITION FROM [RADHE]
GO
execute as login='radhe'

SELECT * FROM sys.server_triggers
GO
revert
GO

and the tigger is working, as you can see when I try to drop the login Radhe:

Conclusion:

Please review your current server side permissions:

SELECT   
     spr.name as loginName
    ,spr.type_desc
    ,spr.is_disabled
    ,(IS_SRVROLEMEMBER ('sysadmin',spr.name)) AS IS_SYSADMIN
    ,spm.permission_name
    ,spm.state_desc
FROM     
    sys.server_principals spr
    inner join
    sys.server_permissions spm ON spm.grantee_principal_id = spr.principal_id
WHERE    
    spm.permission_name = 'CONTROL SERVER'
    OR
    spm.permission_name = 'VIEW ANY DEFINITION'
    OR
    IS_SRVROLEMEMBER ('sysadmin',spr.name) = 1

Please don't grant high fly permissions to public, or use it with care.