Certificate-Signed Stored Procedure: CREATE PROC script doesn't include this

Question

Post reply

Certificate-Signed Stored Procedure: CREATE PROC script doesn't include this

Rich Mechaber

SSChampion

Points: 11096
More actions
August 29, 2012 at 11:19 am

#280896

I've signed a couple of stored procs with certificates, based on this excellent article[/url] by Jonathan Kehayias.

If I right-click those signed procs in SSMS Object Explorer and select [Script Stored Procedure As, CREATE To], however, the signing is not returned as part of the CREATE script.

I don't see any place to add that in the Tools, Options list, either.

I know I can query sys.crypt_properties to find which SP's are signed, but that's not really the solution I'm looking for.

Is there some way to include the original ADD SIGNATURE TO OBJECT::[i]mystoredproc[/i] using SSMS' normal tools? Or is there something about the security model I've missed that makes this impossible?

Thanks,

Rich

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

pretty sure your going to hit the wall trying to script the password;



ADD SIGNATURE TO OBJECT::[TestSendMail]

BY CERTIFICATE [DBMailCertificate]

WITH PASSWORD = '$tr0ngp@$$w0rd';

i was playing around and came up with this, based on the very limited example of that one test proc and certificate. i know it's wrong for other certificate types;



/*

--Results

ADD SIGNATURE TO OBJECT::TestSendMail BY CERTIFICATE DBMailCertificate WITH PASSWORD = '<Cannot Be Scripted>';

*/

select 'ADD SIGNATURE TO OBJECT::'

       + OBJECT_NAME(pr.major_id)

       + CASE

           WHEN pr.crypt_type = 'SPVC'

           THEN ' BY CERTIFICATE '

           + cr.name

           + ' WITH PASSWORD = ''<Cannot Be Scripted>'';'

           ELSE ''

           END

,*

from sys.crypt_properties pr

LEFT OUTER JOIN

sys.certificates cr on pr.thumbprint = cr.thumbprint

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

There are certain areas of SQL Server that are not as well supported by scripting as others. Security, in general, is one of them and certificates in particular.

Because of this, I use the same technique that I use to for all of the poorly-scripted parts of SQL Server: I never create/add them directly, instead I always write a stored procedure to create or add these objects/features, even for one-offs and specific single instances. That way, even though I cannot script the added certificate, I can script the procedure that added the certificate.

This proves very handy when I'm using the Script Generator to bulk-script a whole database (or even just a large part of it), so that I don't have to remember what all of the exceptional/lost parts of the script will be.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Rich Mechaber SSChampion Points: 11096 More actions · Answer 3

Ug. Thank you for testing, Lowell, and thank you for confirming my unhappy expectations, Barry. I do see that your SP work-around might work, but there's a lot of fiddling there if I'm using Jonathan's article as a template (backing cert up to file system and re-loading from there, for example; what if that location moves?).

Do you script out all parts of it, then? Creating the cert, backing it up, signing, etc.? What do you do if you need to access different databases? (I need to authorize a user to execute msdb.dbo.sp_start_job).

Thanks,

Rich

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

It is a lot of effort-overhead. I haven't used Certificates much beyond proc-signing for some cross-database stuff. Let me read Jonathan's article and see if I can better answer your last Questions ...

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

OK, cool, I didn't know you could do all of that.

Anyway, to answer your question, yes, I would put as much of that as possible in a stored procedure.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]