Can I use Extended Events to capture ADD SIGNATURE ...?


Is it possible to capture this event using the "Extended Events" feature:?

 ADD SIGNATURE TO [myStoredProc] BY CERTIFICATE myCertificate

I would like to track who has added the signature to the stored procedure and which procedure was affected.


more ▼

asked Aug 03, 2012 at 12:58 PM in Default

avatar image

2.2k 18 24 31

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

On further research, I dare to say it is not possible ;). Reasons being it is not visible in SQL 2008 R2 XE configurable events. Secondly, this seems to fall in the Security Auditing. And according to Mike Wachal from the Extended Events team at Microsoft

There are some Event Classes that did not make the cut and were not migrated. These fall into two categories; there were a few Event Classes that had been deprecated, or that just did not make sense, so we didn’t migrate them. (You won’t find an Event related to mounting a tape – sorry.) The second class is bigger; with rare exception, we did not migrate any of the Event Classes that were related to Security Auditing using SQL Trace. We introduced the SQL Audit feature in SQL Server 2008 and that will be the compliance and auditing feature going forward. Doing this is a very deliberate decision to support separation of duties for DBAs. There are separate permissions required for SQL Audit and Extended Events tracing so you can assign these tasks to different people if you choose

Although he was talking about SQL 2012, but it gives clear indication that it is something to be taken care of through SQL Audit. For that I created the following SQLAudit

 USE master ;
 -- Create the server audit. 
 CREATE SERVER AUDIT Add_Signature_Audit
     TO FILE ( FILEPATH = 'C:\' ) ; 
 -- Enable the server audit. 
 ALTER SERVER AUDIT Add_Signature_Audit 
 USE Sandbox
 FOR SERVER AUDIT Add_Signature_Audit

And guess what it gives me what is desired (which BTW also is meeting regulatory compliance requirements ;))

But if you cannot use SQL Audit, then I guess you are left with two choices i.e. SQL Trace and DDL TRIGGERS

more ▼

answered Aug 09, 2012 at 10:39 AM

avatar image

Usman Butt
14.9k 6 13 21

Thanks Usman! This is a great research on this topic! Unfortunately I cannot use SQL Audits, this feature requires SQL Server Enterprise Edition :( DDL Triggers do not cover this scope: As far as I can tell there's no trigger scope like ADD_SIGNATURE... :( The last resort will be SQL Trace, but I'm afraid it could harm performance, because I will have to monitor SQL:BatchCompleted-events. Anyway you've answered my question - I appreciate it a lot! :)

Aug 13, 2012 at 08:00 AM eghetto

@eghetto Of-course the same can be achieved with DDL triggers ;) The related DDL events chain is

 |   |   |   |   ADD_SIGNATURE
Aug 15, 2012 at 10:36 AM Usman Butt

@usman-butt: +1! Right - I've overlooked that. Thanks!

Aug 15, 2012 at 10:58 AM eghetto
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 03, 2012 at 12:58 PM

Seen: 1242 times

Last Updated: Aug 15, 2012 at 10:58 AM

Copyright 2018 Redgate Software. Privacy Policy