Cannot find the symmetric key '', because it does not exist or you do not have permission

Question

Using SQL 2005

I created a Master Key encrypted by password and then encrypted the Master Key with the Service Master Key

I then created an ASYMMETRIC_KEY which I use to encrypt my SYMMETRIC_KEY I did all this using the SA user for SQL and logged into windows server as the domain admin.

I have a user in my database that only has dbo permissions on my database and then I grant that user execute permissions on all the necessary stored procedures and I also deny view definition to those same stored procedures.

The stored procedure giving me trouble where I do the encryption is this here. Also this all worked on SQL2012 in my testing pc but now that I am creating this on production server with sql 2005 and it is failing

open symmetric key
    MYKEY_NAME_SYM decryption by Asymmetric key MYKEY_NAME_ASYM
    set @ENCRYPTVARIABLE = (select ENCRYPTBYKEY(KEY_GUID('MYKEY_NAME_SYM'), @ENCRYPTCOLUMN))    
close symmetric key MYKEY_NAME_SYM

The error I am receiving is:

Cannot find the symmetric key 'MKEY_NAME_SYM', because it does not exist or you do not have permission.

So I am guessing that the user I am using in my database does not have permissions but when I try to grant permissions

GRANT 
VIEW DEFINITION ON SYMMETRIC KEY::MKEY_NAME_SYM TO [myuser]

I get the same error cannot find the symmetric key or I don't have permissions.

Answer 1

With an administrative user write in script console:

    --Grant View
    GRANT VIEW DEFINITION ON CERTIFICATE::[Certificate_Name] TO [user]
    GRANT VIEW DEFINITION ON SYMMETRIC KEY::[EncryptionKey_Name] TO [user]

    --Grant Control
    GRANT CONTROL ON CERTIFICATE::[Certificate_Name] TO [user]