Microsoft® SQL Server® 2012 Bible

288

Part II: Building Databases and Working with Data

In the following code example, the text of the view is inspected within sys.sql_

modules, the view is encrypted, and sys.sql_modules is again inspected (as expected,

the SELECT statement for the view is then no longer readable):

SELECT definition

FROM sys.sql_modules

WHERE object_id = OBJECT_ID(N'dbo.vTest');

The result is the text of the vText view:

definition

-----------------------------

CREATE VIEW vTest

WITH SCHEMABINDING

AS

SELECT [Name] FROM dbo.Test;

The following ALTER command rebuilds the view WITH ENCRYPTION:

ALTER VIEW vTest

WITH ENCRYPTION

AS

SELECT [Name] FROM dbo.Test;

Be careful with this option. When the code is encrypted, Management Studio can no longer

produce a script to alter the view and instead generates this message:

/****** Encrypted object is not transferable,

and script cannot be generated. ******/

In addition, be aware that the encryption affects replication. An encrypted view cannot be

published.

Application Metadata

The front-end application or data access layer may request schema information, called

metadata, along with the data when querying SQL Server. Typically, SQL Server returns

schema information for the underlying tables, but the WITH VIEW METADATA option tells

SQL Server to return schema information about the view, rather than the tables referenced

by the view. This prohibits someone from learning about the table’s schema and is useful

when the view’s purpose is to hide sensitive columns.

Using SQL Synonyms

Views are sometimes employed to hide cryptic database schema names. Synonyms are simi-

lar to views, but they are more limited. Whereas views can project columns, assign column

c11.indd 288c11.indd 288 7/30/2012 4:41:13 PM7/30/2012 4:41:13 PM

http://www.it-ebooks.info