You manage a SQL Server 2008 server that hosts a database named Sales. Unauthorized odifications
to some tables within the Sales database have resulted in some views no longer working. You need
to implement a method that will ensure that all modifications to any objects with the Sales database
are logged. What should you do?
A.
Create a DDL trigger with database scope.
B.
Create a DML trigger with database scope.
C.
Create a DML trigger with server scope.
D.
Create a DDL trigger with server scope.
Explanation:
A Database Definition Language (DDL) trigger with database scope can be used to audit all DDL
statements on any database objects. A DDL
statement (such as CREATE, ALTER, and drop) is used to create, alter, or drop (remove or delete)
objects within the database. Once the event
is captured, the DDL trigger can be programmed to log events, or if desired, even roll back
unauthorized events.
Answer D is incorrect. A Database Definition Language (DDL) trigger with server scope can be used
to audit all DDL statements at the
server level, such as the creation, modification, or deletion of a database. However, DDL triggers at
the server level will not capture DDL
statements executed on objects within a database.
Answer B and C are incorrect. Data Manipulation Language (DML) statements (such as UPDATE,
INSERT, or DELETE) are used to
update, insert, or delete data within a database. A DML trigger will not capture modifications to the
objects. Further, DML triggers would be
created with database scope since it works on data within a database.