The ENCRYPTION option is available for views, UDFs, stored procedures, and triggers. If you don’t specify the ENCRYPTION option, SQL Server stores the text defining the body of the object/routine as clear text in sys.sql.modules (or in syscomments in SQL Server 2000). If you specify the ENCRYPTION option, the object’s text will be converted to an obfuscated format. But don’t rely on this option as an encryption mechanism to protect your intellectual property. People have found ways to decrypt text stored for objects created with the ENCRYPTION option. Even in SQL Server 2005 the object’s text will be accessible to privileged users through the dedicated administrator connection (DAC), direct access to the database files or from memory using a debugger. For details on the ENCRYPTION option, please refer to Books Online.

The SCHEMABINDING option binds the schema of the view or UDF to the schema of the underlying objects. If you create a view with the SCHEMABINDING option, SQL Server will reject attempts to drop underlying objects or make any schema modification to referenced columns. This option has two syntactical requirements in terms of the query defining the view: two-part names must be used for all objects (for example, dbo.Orders, not just Orders), and the use of * is not allowed in the SELECT list–rather, all column names must be specified explicitly.

As an example of using both ENCRYPTION and SCHEMABINDING, the following code re-creates the VCustsWithOrders view, which I used earlier in my examples:

USE Northwind;
GO
IF OBJECT_ID('dbo.VCustsWithOrders') IS NOT NULL
  DROP VIEW dbo.VCustsWithOrders;
GO
CREATE VIEW dbo.VCustsWithOrders WITH ENCRYPTION, SCHEMABINDING
AS

SELECT CustomerID, CompanyName, ContactName, ContactTitle,
  Address, City, Region, PostalCode, Country, Phone, Fax
FROM dbo.Customers AS C
WHERE EXISTS
  (SELECT 1 FROM dbo.Orders AS O
   WHERE O.CustomerID = C.CustomerID);
GO

Note

If the view already exists, it’s wiser to use ALTER VIEW than to drop and re-create the view, because ALTER VIEW preserves permissions.

Notice that I substituted the * I used originally in the subquery’s SELECT list with the constant 1 to meet the requirements of the SCHEMABINDING option.

Try to get the text of the view:

EXEC sp_helptext 'dbo.VCustsWithOrders';

You will get the following output:

The text for object 'dbo.VCustsWithOrders' is encrypted.

Try to alter one of the referenced columns:

ALTER TABLE dbo.Customers DROP COLUMN Address;

You will get the following error:

Msg 5074, Level 16, State 1, Line 1
The object 'VCustsWithOrders' is dependent on column 'Address'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE DROP COLUMN Address failed because one or more objects access this column.

Specifying WITH CHECK OPTION when creating a view prevents INSERT and UPDATE statements that conflict with the view’s query filter. Without this option, a view normally accepts modifications that do not meet the query’s filter. For example, the VCustsWithOrders view accepts the following INSERT, even though it inserts a new customer that has no orders yet:

INSERT INTO dbo.VCustsWithOrders(CustomerID, CompanyName)
  VALUES(N'ABCDE', N'Company ABCDE'),

The new customer was added to the Customers table, but obviously when you query the view, you won’t see the new customer because the view contains only customers with orders:

SELECT CustomerID, CompanyName
FROM dbo.VCustsWithOrders
WHERE CustomerID = N'ABCDE';

This query returns an empty set.

If you query the Customers table directly, you will see the new customer:

SELECT CustomerID, CompanyName
FROM dbo.Customers
WHERE CustomerID = N'ABCDE';

This query returns information about customer ABCDE.

Next run the following code to add the CHECK OPTION to the view’s definition:

ALTER VIEW dbo.VCustsWithOrders WITH ENCRYPTION, SCHEMABINDING
AS

SELECT CustomerID, CompanyName, ContactName, ContactTitle,
  Address, City, Region, PostalCode, Country, Phone, Fax
FROM dbo.Customers AS C
WHERE EXISTS
  (SELECT 1 FROM dbo.Orders AS O
   WHERE O.CustomerID = C.CustomerID)
WITH CHECK OPTION;
GO

Note

When altering the view, you must specify again all options that you want to preserve—in our case, ENCRYPTION and SCHEMABINDING. If you don’t mention them in the ALTER statement, they will no longer be in effect.

Now try to insert a row that conflicts with the filter:

INSERT INTO dbo.VCustsWithOrders(CustomerID, CompanyName)
  VALUES(N'FGHIJ', N'Company FGHIJ'),

You will get the following error:

Msg 550, Level 16, State 1, Line 1
The attempted insert or update failed because the target view either specifies WITH CHECK
OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from
the operation did not qualify under the CHECK OPTION constraint.
The statement has been terminated.

SQL Server can control client requests to query or modify data through a view only when the request ends up generating T-SQL code with the view as the target. However, clients that request browse-mode metadata through the DB-Library, ODBC, or OLEDB APIs might cause trouble. Browse-mode metadata is additional metadata about the base tables and columns in the result set that SQL Server returns to these client-side APIs. Of course, if the client chooses to construct statements with the base table as the target instead of the view, user requests might not work as expected.

Suppose that a user was granted permission against a view but not against the underlying tables. The user tries to perform some activity against the view. If the client tool constructs a statement against the base table because it requested browse-mode metadata, such a statement will fail on a security violation. On the other hand, if a user attempts to modify data through a view, and the modification conflicts with a CHECK OPTION defined with the view, such a modification might succeed if submitted directly against the underlying table.

Enterprise Manager (EM) in SQL Server 2000 is one of those tools that requests browse-mode metadata information when you manipulate data through a view graphically (after right-clicking a view name and choosing "Open View" and then "Return all rows"). If you trace the activity that EM submits to SQL Server when you manipulate a view’s data, you will find that the activity is actually submitted with the underlying tables as the target. For example, try to insert a new customer through the VCustsWithOrders view using EM. Specify FGHIJ in the CustomerID column and company FGHIJ as the CompanyName column. You’ll find that although this attempt was supposed to fail on a CHECK OPTION violation, it is accepted, and the row is added to the Customers table. If you trace EM’s activity against SQL Server, you will realize why this attempt was successful. The following statement will show up in the trace:

exec sp_executesql N'INSERT INTO "Northwind"."dbo"."Customers" ("CustomerID","CompanyName")
VALUES (@P1,@P2)', N'@P1 nvarchar(5),@P2 nvarchar(40)',
N'FGHIJ', N'Company FGHIJ'

EM constructed a statement directly against the Customers table instead of against the view. SQL Server Management Studio (SSMS) addressed this problem partially. A similar attempt will still be successful if done in the view/query designer results pane. It will fail if done in the results pane generated by "Open View". For the latter, you will find the following statement in the trace showing that the activity was submitted against the view:

exec sp_executesql N'INSERT INTO VCustsWithOrders(CustomerID, CompanyName) VALUES (@Customer
ID, @CompanyName)',N'@CustomerID
nvarchar(5),@CompanyName nvarchar(13)',@CustomerID=N'FGHIJ',@CompanyName=N'Company FGHIJ'

If you want SQL Server to send metadata information about the view and not the underlying tables when browse mode metadata is requested by the client APIs, specify the VIEW_METADATA option when you create or alter the view, as in:

ALTER VIEW dbo.VCustsWithOrders
  WITH ENCRYPTION, SCHEMABINDING, VIEW_METADATA
AS

SELECT CustomerID, CompanyName, ContactName, ContactTitle,
  Address, City, Region, PostalCode, Country, Phone, Fax
FROM dbo.Customers AS C
WHERE EXISTS
  (SELECT 1 FROM dbo.Orders AS O
   WHERE O.CustomerID = C.CustomerID)
WITH CHECK OPTION;
GO

When you’re done, issue the following cleanup code:

USE Northwind;
GO
DELETE FROM dbo.Customers
WHERE CustomerID IN(N'ABCDE', N'FGHIJ'),
GO
IF OBJECT_ID('dbo.VCustsWithOrders') IS NOT NULL
  DROP VIEW dbo.VCustsWithOrders;
GO