Can I use Extended Events to capture ADD SIGNATURE ...?
Hello! 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. Thanks!
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 ; GO -- Create the server audit. CREATE SERVER AUDIT Add_Signature_Audit TO FILE ( FILEPATH = 'C:\' ) ; GO -- Enable the server audit. ALTER SERVER AUDIT Add_Signature_Audit WITH (STATE = ON) ; GO USE Sandbox GO CREATE DATABASE AUDIT SPECIFICATION Add_Signature_Audit_DB FOR SERVER AUDIT Add_Signature_Audit ADD (DATABASE_OBJECT_CHANGE_GROUP) --OR ADD (SCHEMA_OBJECT_CHANGE_GROUP) WITH (STATE = ON) ; GO 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