Sunday, 17 May 2015

Cannot find the symmetric key 'x', because it does not exist or you do not have permission - Error in SQL Server by serverku

Recently I ran into an issue while executing stored procedure and issue was with permission of symmetric keys. Here I am sharing an error and fix of that issue. Following is the script run against the user database in which we got a problem. Here SymmetricKeyCert is a certificate name and SymmetricKeyTest is a symmetric key name in the example.

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

Fix : 
USE <DB Name>
GO

-- SELECT * FROM sys.certificates
-- Find associated certificate name
-- Grant permission to SYMMETRIC KEY and ON CERTIFICATE

GRANT CONTROL ON CERTIFICATE :: SymmetricKeyCert TO [UserTest]
GO
GRANT REFERENCES ON SYMMETRIC KEY::SymmetricKeyTest TO [UserTest]
GO
GRANT VIEW DEFINITION ON SYMMETRIC KEY::SymmetricKeyTest TO [UserTest]
GO

This is a what I got a solution and I would like to request to share some such type of permission related issues and workaround for same, may it helps to readers. Have a great day.

No comments:

Post a Comment

Please Use Good Leanguage