x

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!
more ▼

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

eghetto gravatar image

eghetto
2.1k 15 17 23

(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 ;
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
more ▼

answered Aug 09, 2012 at 10:39 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

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

|   |   |   DDL_CRYPTO_SIGNATURE_EVENTS
|   |   |   |   ADD_SIGNATURE
|   |   |   |   ADD_SIGNATURE_SCHEMA_OBJECT
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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x582
x8

asked: Aug 03, 2012 at 12:58 PM

Seen: 983 times

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