none
Error Signing a DDL Trigger RRS feed

  • Question

  • Issue:  Error when trying to add signature to DDL triggers.

    My Setup:  I have several databases where I have added DDL triggers at the server and database level.  The users have read, write, and ddl_admin on two of the databases as they are developers.  They do not however have access to the database where I store the auditing tables.  To work around this I have created a Certificate, login via certificate, granted Connect/Insert writes to the login on the audit tables.  Here is where the issue lies:

    SQL OS:  SQL 2005 SP2 STANDARD EDITION

    Command:
    ADD SIGNATURE TO tr_ChangeDatabaseEvents BY CERTIFICATE EvtTriggerCert
    WITH PASSWORD = 'xxxxxxxxxxxx'
    GO

    Error Message:
    Msg 15151, Level 16, State 1, Line 23
    Cannot alter the object 'dbo.tr_ChangeDatabaseEvents', because it does not exist or you do not have permission.

    I cannot seem to get this to work on System or Database level triggers.

    Any help would be much appreciated. 

    I can of course GRANT INSERT/CONNECT to the users, but that is the easy way out and would cause more maintenance then I want.  I would prefer to add a role, grant the role execute rights to the triggers, and add users to the role.

    Tuesday, March 17, 2009 2:38 PM

Answers

  •   Due to implementation limitations, DDL Triggers cannot be digitally signed (we have similar limitations with in-line table valued functions).

       An alternative for your scenario you may be able to use EXECUTE AS in the DDL trigger to get the controlled escalation needed to access restricted resources. In case you need to access the trigger caller’s context, you can use EXECUTE AS CALLER (statement) and REVERT to switch back between the impersonated context and the caller’s context. For example:

     

    1 CREATE TRIGGER trigg_demo  
    2 ON ALL SERVER  
    3 WITH EXECUTE AS 'trigger_login' 
    4 FOR CREATE_LOGIN  
    5 AS 
    6   -- View the token of the impersonated context,   
    7   -- NOTE; Since this is a server-scoped trigger,  
    8   --  the context is a full token and valid across the server  
    9   --  
    10   SELECT suser_sname() as Impersonated_context  
    11   SELECT * FROM sys.login_token  
    12     
    13   -- Switch to the context of the user who executed CREATE LOGIN   
    14   --  
    15   EXECUTE AS CALLER  
    16   SELECT suser_sname() as DDL_Caller  
    17   SELECT * FROM sys.login_token  
    18  
    19   -- Switch back to the trigger-specific context  
    20   --  
    21   REVERT  
    22   SELECT suser_sname() as Impersonated_context  
    23   SELECT * FROM sys.login_token  
    24 go  
    25  
    26  

      I hope this information helps, please let us know if you have any additional question.

     -Raul Garcia
       SDE/T
       SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, March 17, 2009 9:08 PM
    Moderator

All replies

  •   Due to implementation limitations, DDL Triggers cannot be digitally signed (we have similar limitations with in-line table valued functions).

       An alternative for your scenario you may be able to use EXECUTE AS in the DDL trigger to get the controlled escalation needed to access restricted resources. In case you need to access the trigger caller’s context, you can use EXECUTE AS CALLER (statement) and REVERT to switch back between the impersonated context and the caller’s context. For example:

     

    1 CREATE TRIGGER trigg_demo  
    2 ON ALL SERVER  
    3 WITH EXECUTE AS 'trigger_login' 
    4 FOR CREATE_LOGIN  
    5 AS 
    6   -- View the token of the impersonated context,   
    7   -- NOTE; Since this is a server-scoped trigger,  
    8   --  the context is a full token and valid across the server  
    9   --  
    10   SELECT suser_sname() as Impersonated_context  
    11   SELECT * FROM sys.login_token  
    12     
    13   -- Switch to the context of the user who executed CREATE LOGIN   
    14   --  
    15   EXECUTE AS CALLER  
    16   SELECT suser_sname() as DDL_Caller  
    17   SELECT * FROM sys.login_token  
    18  
    19   -- Switch back to the trigger-specific context  
    20   --  
    21   REVERT  
    22   SELECT suser_sname() as Impersonated_context  
    23   SELECT * FROM sys.login_token  
    24 go  
    25  
    26  

      I hope this information helps, please let us know if you have any additional question.

     -Raul Garcia
       SDE/T
       SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, March 17, 2009 9:08 PM
    Moderator
  • Raul, thanks for the update. 

    Any future plans to allow DDL triggers or in-line functions to be signed.  The ability to sign processes within SQL Server is a wonderful addition and makes custom security easier to implement and maintain.

    Thanks Again,

    Brian
    Tuesday, March 17, 2009 11:02 PM
  •    Thanks a lot. We are really glad to hear that our features are able to help our customers. We really appreciate it.

       At the moment we don’t have any plans to extend the ADD SIGNATURE feature, but you are welcomed to give us the feedback at the Microsoft SQL Server Connect website (connect.microsoft.com/sql), and anyone interested in the extension of the feature to vote for the feedback item. 
      The feedback we receive in this website is used to help us triage bugs and feature requests based on their popularity (i.e. votes). 

      -Raul Garcia
       SDE/T
       SQL Server Engine

    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, March 17, 2009 11:13 PM
    Moderator
  •    At the moment we don’t have any plans to extend the ADD SIGNATURE feature, but you are welcomed to give us the feedback at the Microsoft SQL Server Connect website (connect.microsoft.com/sql), 

    I have just posted this as a Connect Suggestion at:

    Allow signing Database DDL Triggers and Server DDL and Logon Triggers - ADD SIGNATURE

    Thursday, January 19, 2017 10:24 PM