I am looking into using certificates and procedure signing as a temporary means of getting around granting direct permissions to the sp_OA procedures to our developers. Eventually, we will, of course, move to using CLR procedures instead of sp_OA but as it stands, I just need a way to give these permissions to our developers through stored procedure signing.

Here’s what I have been able to do and where I am running into issues. I started by creating a stored procedure that was similar to the situation we have in our real production environment. The procedure simply uses the sp_send_dbmail stored procedure to send me an email:

Then I execute the proc to ensure that it works. It does. Then I grant permissions to a user in our database who is not a member of the sysadmin server role, impersonate him, then try to execute the procedure – “you do not have permissions to execute sp_send_dbmail”. This was expected. So I reverted to my own permissions and went to the msdb database. I created a master key that was encrypted by a password and then a certificate:

Then I added a signature to sp_send_dbmail by the certificate I had created and backed the cert up to a file location. (I had also removed the private key before backing up a few of the times I tried this). I created a user from the cert in msdb, granted execute permissions on sp_send_dbmail to the user and also granted authenticate to the user.

I went back to my_database and created cert with same name from file location:

CREATE CERTIFICATE My_Certificate FROM FILE = 'C:\cert_sign.cer'

HERE IS WHERE I AM HAVING MY PROBLEM. When I then try to sign my procedure, P_Send_Mail_Test, I get an error:

ADD SIGNATURE TO P_Send_Mail_Test BY CERTIFICATE My_Certificate

Msg 15556, Level 16, State 1, Line 2Cannot decrypt or encrypt using the specified certificate, either because it has no private key or because the password provided for the private key is incorrect.

In other articles, I have seen examples where the author makes use of a private key and not the master key. I would be more than happy to try this but do not know how to create a private key. I even tried, when signing the procedure, using the signature from crypt_properties – didn’t work. And also tried providing the password I used when creating the master key: