question

eghetto avatar image
eghetto asked

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!
sql-server-2008-r2extended-events
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Usman Butt avatar image
Usman Butt answered
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
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@eghetto Of-course the same can be achieved with DDL triggers ;) The related DDL events chain is | | | DDL_CRYPTO_SIGNATURE_EVENTS | | | | ADD_SIGNATURE | | | | ADD_SIGNATURE_SCHEMA_OBJECT
1 Like 1 ·
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! :)
0 Likes 0 ·
@usman-butt: +1! Right - I've overlooked that. Thanks!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.