Grant appropriate permission to use Symmetric Key in stored proc

Question

I created a symmetric key in SQL2012 using the following code (logged in as Windows Admin user):

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '34trg45trgf546t';
CREATE CERTIFICATE SSCert01 WITH SUBJECT = 'SS Certificate 01';
CREATE SYMMETRIC KEY SSN_Key_01 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE SSCert01;

Once that was done I applied encryption to certain database columns. Still logged in as Admin, I can successfully decrypt columns using the Key:

OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE SSCert01;
SELECT
    name,
    surname,
    CONVERT(nvarchar(50),DECRYPTBYKEY(PasswordEnc)) as DecryptedPassword
FROM
    [tbl_Users];
CLOSE SYMMETRIC KEY SSN_Key_01;

I then placed the above code into a Stored Procedure. The problem is that my application access SQL using two Roles, which access the appropriate proc's. When either of these two Roles tries to execute a proc containing the above code, I see this error:

Cannot find the certificate 'SSCert01', because it does not exist or you do not have permission. The key 'SSN_Key_01' is not open. Please open the key before using it.

When I login as either Role, they cannot see the Key or the Cert. So, can anyone advise WHICH permissions to grant to the roles so that they can use the key/cert within stored procedures (only) to encrypt/decrypt data. The roles shouldn't be allowed to perform any functionality with the key/cert apart from encryption/decryption. I have looked at MSDN/Google and am none the wiser.

UPDATE The following code allows the roles to use the proc's, but I am worried that CONTROL is too much access. Can anyone provide some clarity please?

GRANT CONTROL ON CERTIFICATE :: SSCert01 TO Role001;
GRANT CONTROL ON SYMMETRIC KEY :: SSN_Key_01 TO Role001;

Solution

The way I usually get around this is to set the procedure to execute as owner and then make sure that the owner of the procedure has the correct permissions to perform the decryption, a lot of time the owner of the proc is DBO anyway so no additional configuration needs to be done apart from altering the procedure like so:

ALTER PROCEDURE proc_name
WITH EXECUTE AS OWNER
AS
OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE SSCert01;
SELECT
    name,
    surname,
    CONVERT(nvarchar(50),DECRYPTBYKEY(PasswordEnc)) as DecryptedPassword
FROM
    [tbl_Users];
CLOSE SYMMETRIC KEY SSN_Key_01;

This means that you don't have to grant any additional permissions at all to your application role or users.

Comments

[EvilDr]

Thanks, I wasn't aware of EXECUTE AS. Not sure if that's more a workaround though...?

[Andrew Savinykh]

@EvilDr, well, that's how you do it. It does not seem that there is a better way. You need CONTROL permission on the cert to decrypt the key. If you don't want to grant that to the role use a stored procedure as indicated in this answer

[EvilDr]

Okay great. Does GRANT CONTROL allow a Role/User to DROP or ALTER the certificate/key?

[steoleary]

Yes, CONTROL allows the user to drop the key.

[EvilDr]

Accepted as answer plus awarded bounty. This approach is much cleaner than granting permissions to users/roles. Thank you @steoleary for your input.

[Lee]

This is great and seems more secure than granting permissions +1