Professional SQL Server™ 2005 Programming

9.6. Protecting Code: Encrypting Views

If you're building any kind of commercial software product, odds are that you're interested in protecting your source code. Views are the first place we see the opportunity to do just that.

All you have to do to encrypt your view is use the WITH ENCRYPTION option. This one has a couple of tricks to it if you're used to the WITH CHECK OPTION clause:

• WITH ENCRYPTION goes after the name of the view, but before the AS keyword.

• WITH ENCRYPTION does not use the OPTION keyword.

In addition, remember that if you use an ALTER VIEW statement, you are entirely replacing the existing view except for access rights. This means that the encryption is also replaced. If you want the altered view to be encrypted, then you must use the WITH ENCRYPTION clause in the ALTER VIEW statement.

Let's do an ALTER VIEW on the CustomerOrders_vw view that we created earlier in the chapter. If you haven't yet created the CustomerOrders_vw view, then just change the ALTER to CREATE (don't forget to run this against AdventureWorks):

ALTER VIEW CustomerOrders_vw
WITH ENCRYPTION
AS
SELECT   o.SalesOrderID,
         o.OrderDate,
         od.ProductID,
         p.Name,
         od.OrderQty,
         od.UnitPrice,
         od.LineTotal
FROM Sales.SalesOrderHeader AS o
JOIN   Sales.SalesOrderDetail AS od
    ON o.SalesOrderID = od.SalesOrderID
JOIN Production.Product AS p
    ON od.ProductID = p.ProductID

Now do an sp_helptext on our CustomerOrders_vw:

EXEC sp_helptext CustomerOrders_vw

SQL Server promptly tells us that it can't do what we're ...