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

Question

Using SQL 2005

I created a master key encrypted with a password, and then encrypted the master key with the service master key

Then I created ASYMMETRIC_KEY, which I use to encrypt my SYMMETRIC_KEY. I did all this using a SA user for SQL and logged on to a Windows server as a domain administrator.

I have a user in my database that has only dbo permissions in my database, and then I grant the user the right to execute all the necessary stored procedures, and also prohibit definition definitions to the same stored procedures.

The stored procedure gives me problems when I do encryption, this is it. Also, all this worked on SQL2012 on my test pc, but now that I create it on a production server with sql 2005 and it fails

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 getting is:

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

So, I assume that the user I use 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 do not have rights.

Answer 1

The administrative user will write a script in the 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]

Answer 2

I needed to switch SQL to use Windows authentication, as the only user who had permissions was the user of the domain \ admin, which is the user with whom I created the key.

As soon as I switched to Windows authentication, I was able to provide key permissions, and everything was fine