sql-server-2008 - 无法通过证书作为SQL用户帐户打开对称 key

Closed. This question is off-topic。它当前不接受答案。












想改善这个问题吗？ Update the question，所以它是用于堆栈溢出的on-topic。

已关闭8年。



Improve this question




我使用如下证书创建密钥:
CREATE SYMMETRIC KEY some_Key_01
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE myCert;
GOGRANT VIEW DEFINITION ON CERTIFICATE::myCert TO sqlUser;
GOGRANT VIEW DEFINITION ON SYMMETRIC KEY::some_Key_01 TO sqlUser;
GO
然后，我尝试打开并将其打开，如下所示:
OPEN SYMMETRIC KEY some_Key_01
DECRYPTION BY CERTIFICATE myCert;
GO
SELECT CONVERT(datetime, DecryptByKey(EncryptedField))
AS 'DecryptedField'
FROM MyTable;
GO
但是我得到消息:Msg 15151, Level 16, State 1, Line 23
Cannot find the certificate 'myCert', because it does not exist or you do not have permission.

最佳答案

您需要对证书具有control权限:

GRANT CONTROL ON CERTIFICATE::myCert TO sqlUser;
GO

并在密钥上授予references权限:

GRANT REFERENCES ON SYMMETRIC KEY::some_Key_01 TO sqlUser;
GO