You work as DBA at AIOTestKing.com. You administer a SQL Server 2005 computer named AIOTestKingA. AIOTestKing A hosts a database named DB1. DB1 serves as the backend database for an application that tracks issues that help desk technicians receive and the associated trouble tickets. When installed, the help desk application loaded several stored procedures and views that are used for data manipulation and reporting purposes. DB1 contains more than 50 tables. The bulk of the data is stored within the Employees, Issues, Assets, and AssetTracker tables. An application developer inadvertently deletes the AssetTracker table in DB1. The AssetTracker table is referenced by several views and functions in the database. You need to ensure that users are not able to remove critical tables such as this that are referenced by views or functions. What should you do?
A.
Create a new read-only filegroup for DB1 and move all tables to the new filegroup.
B.
Use the ALTER SCHEMA Transact-SQL statement to add all critical tables to the same schema. Configure the DENY-DELETE permission for the Public role on the schema.
C.
Identify the dependencies of each critical table. Alter the syntax of each view and function to include the SCHEMABINDING syntax.
D.
Identify the dependencies of each view and function. Configure the DENY-CONTROL permission for the Public role on each critical table listed as a dependency.
Explanation:
SCHEMABINDING binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified.