Home » Microsoft » MB6-886 » Which action should you take?
You are a database developer on an instance of SQL Server 2008. You are creating a view that will allow users to query information for customer orders. Some users have full database access. You want to prevent these users from viewing the SELECT statement that defines the view. Which action should you take?
A. Delete the view from sys.objects.
B. Alter the view to remove schema binding.
C. Drop the view and re-create it including WITH ENCRYPTION.
D. Implement Transparent Data Encryption (TDE) for the view.
Correct Answer: C
Explanation/Reference:
The WITH ENCRYPTION clause is used to encrypt the CREATE VIEW statement that is stored in the sys.syscomments table. This ensures that the statement is not stored in plain text that is readable by others. When a view is encrypted, the view’s definition cannot be accessed using the sp_helptext system stored procedure, directly queried from the sys.sql_modules catalog view, or accessed from the Visual Designer in SQL Server Management Studio.
You should not delete the view from sys.objects. You should never directly alter data in system objects, but rather allow SQL Server to manage them.
You should not alter the view to remove schema binding. Schema binding of a view ensures that the base tables used by the view remain usable; it does not control whether the statement that defines the view is encrypted or stored as plain text. The WITH SCHEMABINDING clause of the CREATE VIEW statement ensures that base tables of a view cannot be dropped or modified in a way that affects the view’s definition. This prevents users from dropping or modifying base tables in such a way that the view becomes unusable. To drop base tables or make such modifications, you would need to first drop the view, alter the view omitting SCHEMABINDING, or alter the view to remove any unwanted dependencies.
You should not implement Transparent Data Encryption (TDE) for the view. TDE is a special type of full database encryption that uses a symmetric key to encrypt the entire database. It is not used to encrypt the statements that define views, functions, stored procedures, or triggers.