In SQL Server, you can encrypt a view at the time you first create it, or you can alter it later to include encryption.

To create a view with T-SQL, you use the CREATE VIEW syntax. To encrypt it, you add the WITH ENCRYPTION argument.

You can also use the same argument to encrypt an existing view when using ALTER VIEW.

The encrypted view’s text is not directly visible in any catalog views. Therefore, the view’s definition cannot be viewed by users that have no access to system tables or database files.

Using WITH ENCRYPTION also prevents the view from being published as part of SQL Server replication.

Example 1 – Create an Encrypted View

Here’s an example of creating an encrypted view.

CREATE VIEW dbo.v_Cats
WITH ENCRYPTION
AS
    SELECT 
        CatId,
        CatName,
        Phone
    FROM dbo.Cats;
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 view, 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('dbo.v_Cats');

Result:

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

I get the same result with the (deprecated) sys.syscomments catalog view.

SELECT text
FROM sys.syscomments
WHERE id = OBJECT_ID('v_Cats');

Result:

+--------+
| text   |
|--------|
| NULL   |
+--------+

I get a similar result, regardless of which T-SQL method I use to try to get the view’s definition.

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

No script was returned when scripting as Create on object View

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 – Add Encryption to an Existing View

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

ALTER VIEW dbo.v_Cats
WITH ENCRYPTION
AS
    SELECT 
        CatId,
        CatName,
        Phone
    FROM dbo.Cats;
GO

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

The easiest way to ensure that you’re using the same definition is to use your GUI tool to script the existing view 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 3 – Adding Multiple Attributes

Encryption is just one of several attributes you can include in your view’s definition. If you need to specify multiple attributes, separate them by a comma.

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.

ALTER VIEW dbo.v_Cats
WITH ENCRYPTION, SCHEMABINDING
AS
    SELECT 
        CatId,
        CatName,
        Phone
    FROM dbo.Cats;
GO