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

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.

Continue reading...