Allow a stored procedure to decrypt an encrypted column

Question

I created a symmetric key in SQL Server 2016 and then encrypted a single column in the table (PhoneNo).

If I run the following code, the column is decrypted and displays the result:

OPEN SYMMETRIC KEY SymKey_Test DECRYPTION BY CERTIFICATE Certificate_Test;

SELECT        
    ClientContactTest.ClientID, ClientContactTest.ContactID, 
    ClientContactTest.Title, ClientContactTest.Forename, 
    ClientContactTest.Surname, 
    CONVERT(varchar, DecryptByKey(ClientContactTest.PhoneNo_Encrypt)) AS PhoneNo,  
    ClientContactTest.MobileNo, ClientContactTest.EMailAddress, 
    Lookup_ContactType.Description AS ContactTypeDescription
FROM
    ClientContactTest 
LEFT OUTER JOIN
    Lookup_ContactType ON ClientContactTest.ContactTypeID = Lookup_ContactType.ContactTypeID
WHERE
    (ClientContactTest.ClientID = 7) 
    AND (ClientContactTest.SiteID = 0) 
    AND (ClientContactTest.ContactID = 1)

CLOSE SYMMETRIC KEY SymKey_Test

BUT if I put the exact same code inside a stored procedure it returns NULL for this column:

CREATE PROCEDURE [dbo].[ClientContactTest_LoadRecord_Encrypted]
AS
BEGIN
    OPEN SYMMETRIC KEY SymKey_Test DECRYPTION BY CERTIFICATE Certificate_Test;

    SELECT        
        ClientContactTest.ClientID, ClientContactTest.ContactID, 
        ClientContactTest.Title, ClientContactTest.Forename, 
        ClientContactTest.Surname, 
        CONVERT(varchar, DecryptByKey(ClientContactTest.PhoneNo_Encrypt)) AS PhoneNo, 
        ClientContactTest.MobileNo, ClientContactTest.EMailAddress, 
        Lookup_ContactType.Description AS ContactTypeDescription
    FROM
        ClientContactTest 
    LEFT OUTER JOIN
        Lookup_ContactType ON ClientContactTest.ContactTypeID = Lookup_ContactType.ContactTypeID
    WHERE
        (ClientContactTest.ClientID = 7) 
        AND (ClientContactTest.SiteID = 0) 
        AND (ClientContactTest.ContactID = 1)

    CLOSE SYMMETRIC KEY SymKey_Test
END

The stored procedure has execute permission to a role called CRMObjects.

How do I get the correct result returned?

Tried the following steps, but those made no difference:

GRANT CONTROL ON CERTIFICATE :: Certificate_Test TO CRMObjects;
GRANT CONTROL ON SYMMETRIC KEY :: SymKey_Test TO CRMObjects

GRANT VIEW DEFINITION ON SYMMETRIC KEY::SymKey_Test TO CRMObjects
GRANT VIEW DEFINITION ON Certificate::[Certificate_Test] TO CRMObjects

Answer 1

I did finally solve this so thought it a good idea to post what I eventually did

-- Create Master Key, Certificate & Symetric Key

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mzkvdlk979438teag$$ds987yghn)(*&4fdg^';  
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mzkvdlk979438teag$$ds987yghn)(*&4fdg^';  
CREATE CERTIFICATE Certificate_Test2 WITH SUBJECT = 'Test 2'  
CREATE SYMMETRIC KEY SSN_Key_01 WITH ALGORITHM = DES ENCRYPTION BY CERTIFICATE Certificate_Test2;  
GO  


-- Open Key, Encrypt Field & Close Key

OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE Certificate_Test2 ;  
UPDATE ClientContactTest  
SET PhoneNo_Encrypt  
    = EncryptByKey(Key_GUID('SSN_Key_01'), PhoneNo);  
GO  
CLOSE SYMMETRIC KEY SSN_Key_01;  

-- And Test It

OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE Certificate_Test2;
SELECT        CONVERT(varchar(20), DecryptByKey(ClientContactTest.PhoneNo_Encrypt)) AS PhoneNo 
FROM            ClientContactTest
WHERE        (ClientContactTest.ClientID = 7)

-- Create Stored Procedure

CREATE PROCEDURE [dbo].[ClientContact_LoadRecord_Encrypted] 
AS
BEGIN
    OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE Certificate_Test2;
    SELECT        CONVERT(varchar(20), DecryptByKey(ClientContactTest.PhoneNo_Encrypt)) AS PhoneNo 
    FROM            ClientContactTest
    WHERE        (ClientContactTest.ClientID = 7)
    CLOSE SYMMETRIC KEY SSN_Key_01
END

As I then run this via a VB.NET Front end the final stage is to give permissions to the Role (In my case called CRMObjects)

GRANT CONTROL ON CERTIFICATE :: Certificate_Test2 TO CRMObjects;
GRANT CONTROL ON SYMMETRIC KEY :: SSN_Key_01 TO CRMObjects