Running Function under SQL ID returns NULL columns

Question

I have a function that is encrypting and decrypting columns. When I run the function under a windows ID, all of the values are returned, however, when I query the function using a SQL Server ID, the decrypted columns are NULL.

The function is using DECRYPTBYKEYAUTOCERT(CERT_ID('certname'),

I'm thinking a permission level issue with the SQL Server ID, but not 100% how to verify that or change it. I did log into SQL using that SQL ID and ran the function (select * from getMyDate() ) and the decrypted fields are NULL.

thanks

Answer 1

If you look at https://learn.microsoft.com/en-us/sql/t-sql/functions/decryptbykeyautocert-transact-sql?view=sql-server-ver16#permissions, you will find that it says:

Requires VIEW DEFINITION permission on the symmetric key, and CONTROL permission on the certificate.

Apparently you Windows account has these permissions, while the SQL ID has not.

Answer 2

Hi @TheCoder

Using the Windows account gets the result, while the SQL Server ID returns null.

As expert says, the SQL Server ID does not have sufficient permissions to access the certificate or the symmetric key.

You can use the GRANT statement to assign these permissions to the SQL Server ID.

https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-transact-sql?view=sql-server-ver16

Best regards,

Percy Tang