0

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

0

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:

enter image description here

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:

enter image description here

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

GRANT VIEW ANY DEFINITION TO [RADHE]
GO

enter image description here

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

enter image description here

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

enter image description here

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.

| improve this answer | |
  • 1
    Thanks for detailed answer. I was talking about managed instances, where you can see that they have some server level triggers, but can not see it's definition – Andy Apr 10 at 17:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.