Home » Microsoft » 70-462 v.2 » Which four Transact-SQL statements should you use?
DRAG DROP
You administer a Microsoft SQL Server database server that has a database named Contoso. The Contoso database has a table named ProductPrices in a schema named Sales.
You need to create a script that writes audit events into the application log whenever data in the ProductPrices table is updated.
Which four Transact-SQL statements should you use? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)
Select and Place:
Correct Answer:
Explanation/Reference:
Explanation:
Example:
The following example creates a server audit called Payrole_Security_Audit and then a database audit specification called Payrole_Security_Audit that audits
SELECT and INSERT statements by the dbo user, for the HumanResources.EmployeePayHistory table in the AdventureWorks2012 database.
Copy
USE master ;
GO
— Create the server audit.
CREATE SERVER AUDIT Payrole_Security_Audit
TO FILE ( FILEPATH =
‘C:Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLDATA’ ) ;
GO
— Enable the server audit.
ALTER SERVER AUDIT Payrole_Security_Audit
WITH (STATE = ON) ;
GO
— Move to the target database.
USE AdventureWorks2012 ;
GO
— Create the database audit specification.
CREATE DATABASE AUDIT SPECIFICATION Audit_Pay_Tables
FOR SERVER AUDIT Payrole_Security_Audit
ADD (SELECT , INSERT
ON HumanResources.EmployeePayHistory BY dbo )
WITH (STATE = ON) ;
GO
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-database-audit-specification-transact-sql