Allow signing Database DDL Triggers and Server DDL and Logon Triggers - ADD SIGNATURE - by Solomon Rutzky

Status : 

 


7
0
Sign in
to vote
ID 3119169 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 1/19/2017 1:58:34 PM
Access Restriction Public

Description

It is not currently possible to sign non-Schema-scoped Triggers. This means that Database-scoped DDL Triggers along with Server-scoped DDL Triggers and Logon Triggers cannot (easily) participate in all of the wonderful benefits of Module Signing. Yes, there are two works around -- use EXECUTE AS, and creating a stored procedure that the Trigger calls and passes the EVENTDATA() XML into -- but both are clunky: EXECUTE AS comes with a host of issues, and creating a stored procedure requires managing that object, and either granting EXECUTE to [public] on it or doing something else kinda silly with permissions.

This desire has come up in the following places:

1.  Error Signing a DDL Trigger ( https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1333eecd-4c66-43d4-ab8f-03511cad4174 ).

2.  Signatures & Database Triggers ( https://www.sqlservercentral.com/Forums/Topic1201441-359-1.aspx ).

3.  Auditing - sp_send_dbmail from server-level triggers failed ( http://dba.stackexchange.com/questions/161624/auditing-sp-send-dbmail-from-server-level-triggers-failed/161662#161662 ).
Sign in to post a comment.