Running Function under SQL ID returns NULL columns

TheCoder 91 Reputation points

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.


SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,567 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 92,881 Reputation points

    If you look at, 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.

    1 person found this answer helpful.
    0 comments No comments

  2. PercyTang-MSFT 11,806 Reputation points Microsoft Vendor

    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.

    Best regards,

    Percy Tang

    0 comments No comments