Sql-server – How to deny viewing definition of server level trigger

encryptionpermissionssql servertrigger

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

Best Answer

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.

Related Solutions

Sql-server – Deny view definition at DB level and symmetric keys

From BOL:

any permission denial overrides all grants

The implication is that when you DENY permissions to an object those permissions override any GRANTed permissions applicable to that object.

Having said that, you don't need to explicitly deny "view definition" permissions on procedures and other code. If you don't grant VIEW DEFINITION the definitions are not visible, even if the code can be executed. Tables work slightly differently; you'll need to deny view definition to any tables the role has SELECT rights on.

You can test this using the code below.

Create a test database:

USE master;
GO
IF EXISTS (SELECT 1 FROM master.sys.databases d WHERE d.name = 'TestPerms_20160921')
BEGIN
    DROP DATABASE TestPerms_20160921;
END;
GO
CREATE DATABASE TestPerms_20160921;
GO
USE TestPerms_20160921;
GO

Create a table, and a stored proc:

IF OBJECT_ID('dbo.Test') IS NOT NULL
DROP TABLE dbo.Test;
CREATE TABLE dbo.Test
(
    TestID INT NOT NULL
);
GO
IF OBJECT_ID('dbo.TestProc') IS NOT NULL
DROP PROCEDURE dbo.TestProc;
GO
CREATE PROCEDURE dbo.TestProc
AS
BEGIN
    SELECT 1;
END
GO

Create a test user, and a test role:

IF EXISTS (
    SELECT 1 
    FROM sys.database_principals dp 
    WHERE dp.name = 'TestU'
    )
BEGIN
    DROP USER TestU;
END
CREATE USER TestU WITHOUT LOGIN;

IF EXISTS (
    SELECT * 
    FROM sys.database_principals dp 
    WHERE dp.name = 'dbtestrole'
    )
BEGIN
    DROP ROLE dbtestrole;
END
CREATE ROLE dbtestrole;

ALTER ROLE dbtestrole ADD MEMBER TestU;

Grant SELECT and EXECUTE to the test objects:

GRANT SELECT ON dbo.Test TO dbtestrole;
GRANT EXECUTE ON dbo.TestProc TO dbtestrole;

Impersonate the user so we can see the effect of the GRANT statements above:

EXECUTE AS USER = 'TestU';

/* column names and types **are** visible */    
SELECT s.name
    , t.name
    , c.name
    , ty.name
FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    INNER JOIN sys.columns c ON t.object_id = c.object_id
    INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
WHERE s.name = 'dbo'
    AND t.name = 'Test'
ORDER BY s.name
    , t.name
    , c.name;

/* access to view the definition is denied */
EXEC sys.sp_helptext @objname = 'dbo.TestProc', @columnname = NULL;

/* we can run the proc! */
EXEC dbo.TestProc;

REVERT;

DENY VIEW DEFINITION on the table, and test again:

DENY VIEW DEFINITION ON dbo.Test TO dbtestrole;

EXECUTE AS USER = 'TestU';

/* no rows returned as we can no longer view the table definition */
SELECT s.name
    , t.name
    , c.name
    , ty.name
FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    INNER JOIN sys.columns c ON t.object_id = c.object_id
    INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
WHERE s.name = 'dbo'
    AND t.name = 'Test'
ORDER BY s.name
    , t.name
    , c.name;

/* we can still run the proc */
EXEC dbo.TestProc;

/* we can still see the content of the table */
SELECT *
FROM dbo.Test;

REVERT;

Cleanup:

USE master;
GO
IF EXISTS (SELECT 1 FROM master.sys.databases d WHERE d.name = 'TestPerms_20160921')
DROP DATABASE TestPerms_20160921;

Sql-server – sysadmin cannot drop login when ddl audit trigger enabled

After a considerable amount of testing, I finally discovered the reason behind this error. The client connection explicitly set ANSI_WARNINGS and CONCAT_NULL_YIELDS_NULL OFF. XML data operations, such as @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), require both to be ON. I had attempted to override these within the trigger, but I may have placed them wrong. The final code below works, even with the explicit SET options in the connections from Great Plains:

CREATE TRIGGER [ddl_log] 
ON ALL SERVER 
FOR DDL_DATABASE_LEVEL_EVENTS, DDL_SERVER_LEVEL_EVENTS 
AS
BEGIN
SET NOCOUNT ON;
SET ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL ON;

DECLARE @data XML
SET @data = EVENTDATA() 

EXECUTE AS LOGIN='<dummy login>'
INSERT admin.dbo.ddl_audit (PostTime, DB_User, [Event], [TSQL], Host, DatabaseName)
VALUES (
   GETDATE(),
   CONVERT(NVARCHAR(100), ORIGINAL_LOGIN()),
   @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), 
   @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'), 
   CONVERT(NVARCHAR(100), HOST_NAME()),
   @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(100)')
   ) ;
REVERT 
END

As an alternative, I also could have simply inserted EVENTDATA() as an XML LOB into a table, rather than parsing it out into columns. Because I would not be manipulating the XML, the SET options do not matter. Then I would just build an XML index for querying performance, and construct a view to use for audit log reporting that parses the XML in the view definition, in the same manner as I am doing above in my INSERT statement.

Thanks to Max for pointing me in a different research direction, and @AaronBertrand on #sqlhelp who helped me with correct SET options within the body of the trigger.