Invalid use of a side-effecting operator 'OPEN MASTER KEY' within a function

Recently i have tried to create a simple function which should take a single nvarchar parameter as input and should return me its varbinary version, after encryption.When i executed my script, it failed with an error:

Msg 443, Level 16, State 14, Procedure fnc_EncryptNVarcharData, Line 7

Invalid use of a side-effecting operator 'OPEN MASTER KEY' within a function.

Msg 443, Level 16, State 14, Procedure fnc_EncryptNVarcharData, Line 8

Invalid use of a side-effecting operator 'OPEN SYMMETRIC KEY' within a function.

Following is the script, which i had used.

CREATE FUNCTION [dbo].[fnc_EncryptNVarcharData] ( @value nvarchar(2000) )

RETURNS VARBINARY(2000)

AS BEGIN

    DECLARE @EncryptedValue VARBINARY(2000)

  

    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'iwillwin'   

    OPEN SYMMETRIC KEY UserKey DECRYPTION BY CERTIFICATE Usercert   

    DECLARE @KeyGuid AS UNIQUEIDENTIFIER   

    SET @KeyGuid = key_guid('UserKey')   

   

    SELECT  @EncryptedValue = encryptByKey(Key_GUID('UserKey'), @value)

   

    RETURN ( @EncryptedValue )

   

   END

 Problem occurs when you use OPEN MASTER KEY or OPEN SYMMETRIC KEY inside your function.
Solution is very simple.Remove these problematic lines from your function and write before using this function.

New script for this function would be like this

CREATE FUNCTION [dbo].[fnc_EncryptNVarcharData] ( @value nvarchar(2000) )

RETURNS VARBINARY(2000)

AS BEGIN

    DECLARE @EncryptedValue VARBINARY(2000)

  

    --OPEN MASTER KEY DECRYPTION BY PASSWORD = 'iwillwin'   

    --OPEN SYMMETRIC KEY UserKey DECRYPTION BY CERTIFICATE Usercert   

    DECLARE @KeyGuid AS UNIQUEIDENTIFIER   

    SET @KeyGuid = key_guid('UserKey')   

   

    SELECT  @EncryptedValue = encryptByKey(Key_GUID('UserKey'), @value)

   

    RETURN ( @EncryptedValue )

   

   END

And now when i like to use this function, i will mention OPEN MASTER KEY and OPEN SYMMETRIC KEY statements, first.

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'iwillwin'   

OPEN SYMMETRIC KEY UserKey DECRYPTION BY CERTIFICATE Usercert

   

SELECT  CONVERT(nvarchar(200), DECRYPTBYKEYAUTOCERT(CERT_ID('Usercert'), NULL,                                               [dbo].[fnc_encryptNVarchardata](N'ConnectSql')))