When creating a user-defined function in SQL Server, you have the option of encrypting it.

To create a user-defined function with T-SQL, you use the CREATE FUNCTION syntax. To encrypt it, you add the WITH ENCRYPTION argument.

You can also use the same argument to encrypt an existing function when using ALTER FUNCTION.

When you encrypt a user-defined function in this way, the function’s text is converted to an obfuscated format. The function’s definition is not directly visible in any catalog views. Therefore, the function’s definition cannot be viewed by users that have no access to system tables or database files.

Example 1 – Inline Table-Valued Function with Encryption

Here’s an example of creating an encrypted user-defined table-valued function.

CREATE FUNCTION dbo.udf_CatsByName_ITVF( @CatName varchar(70) )
    RETURNS TABLE
    WITH ENCRYPTION
AS
RETURN (
    SELECT 
        CatId,
        CatName,
        Phone
    FROM dbo.Cats
    WHERE CatName = @CatName
    );

GO

The part for encrypting it is WITH ENCRYPTION. I could simply remove that argument if I didn’t want to encrypt it.

After creating that function, now when I use the sys.sql_modules system catalog view to view its definition, I get NULL.

SELECT definition 
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('udf_CatsByName_ITVF');

Result:

+--------------+
| definition   |
|--------------|
| NULL         |
+--------------+

And here’s the error message I get in Azure Data Studio when I try to script the function:

No script was returned when scripting as Create on object UserDefinedFunction

And I would get a similar message if I tried to view it in SSMS, DBeaver, or any other GUI database management software.

Example 2 – Multi-Statement Table-Valued Function with Encryption

Here’s a multi-statement TVF that does the same as the previous function. Multi-Statement TVFs have a different syntax to inline TVFs. On Multi-Statement TVFs, you put the encryption option after you specify the return variable.

CREATE FUNCTION [dbo].[udf_CatsByName_MSTVF]( @CatName varchar(70) )
    RETURNS @cats TABLE (
        CatId int,
        CatName varchar(70),
        Phone varchar(10)
    )
    WITH ENCRYPTION
AS
BEGIN
    INSERT INTO @cats
    SELECT 
        CatId,
        CatName,
        Phone
    FROM dbo.Cats
    WHERE CatName = @CatName;

    RETURN;
END;

GO

Example 3 – Scalar Function with Encryption

And here’s an example of an encrypted scalar function:

CREATE FUNCTION dbo.discountPrice( 
    @price DECIMAL(12,2), 
    @discount DECIMAL(12,2) 
    ) 
RETURNS DECIMAL (12,2) 
WITH ENCRYPTION
AS
BEGIN
  RETURN @price * (1 - @discount);
END;
GO

Example 4 – Add Encryption to an Existing Function

If you want to encrypt an existing function, use ALTER FUNCTION with the same definition. In other words, I can take the first example, and replace CREATE with ALTER.

ALTER FUNCTION dbo.udf_CatsByName_ITVF( @CatName varchar(70) )
    RETURNS TABLE
    WITH ENCRYPTION
AS
RETURN (
    SELECT 
        CatId,
        CatName,
        Phone
    FROM dbo.Cats
    WHERE CatName = @CatName
    );

GO

This obviously assumes that the rest of the function’s definition is exactly the same as the existing function.

The easiest way to ensure that you’re using the same definition is to use your GUI tool to script the existing function using the “Script as Alter” option, if it exists. Otherwise you could use “Script as Create”, then when the definition appears, change CREATE with ALTER.

If you only have a command line interface, you could query the sys.sql_modules view to get the existing definition (like in the previous example). You can then copy the definition and replace CREATE with ALTER.

Once you’ve done that, you can add WITH ENCRYPTION and run it again.

Example 5 – Adding Multiple Arguments

You can specify multiple arguments as a comma-separated list. For example, if you want to use encryption and you want to specify schema binding, then you’d need to add these as a comma separated list.

CREATE FUNCTION dbo.udf_CatsByName_ITVF( @CatName varchar(70) )
    RETURNS TABLE
    WITH SCHEMABINDING, ENCRYPTION
AS
RETURN (
    SELECT 
        CatId,
        CatName,
        Phone
    FROM dbo.Cats
    WHERE CatName = @CatName
    );

GO

In other words, you only specify WITH once – no need to repeat it for each argument.

Important Notes

Here are some things you should know about encrypting user-defined functions in SQL Server:

• Privileged users that can access system tables over the DAC port or directly access database files will still be able to view the function’s (non-encrypted) definition.
• Users that can attach a debugger to the server process can retrieve the original procedure from memory at runtime.
• Using encryption prevents the function from being published as part of SQL Server replication.
• CLR functions cannot be encrypted.