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. Whatshould
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 usedto 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. ADML 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.