PrepAway - Latest Free Exam Questions & Answers

Which four Transact-SQL statements should you use?

DRAG DROP
You administer a Microsoft SQL Server 2012 server that has a database named Contoso. The Contoso
database has a table named EmployeeSalary in a schema named HumanResources.
You need to create a script that writes audit events into the application log whenever data in the
EmployeeSalary table is modified.

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.)

PrepAway - Latest Free Exam Questions & Answers

Answer: See the explanation

Explanation:
Box 1: Use Master
Box 2:

Box 3: Use Contoso
Box 4:

Note:
* An audit must exist before creating a server audit specification for it. When a server audit
specification is created, it is in a disabled state.
* The general process for creating and using an audit is as follows.
Create an audit and define the target.
Create either a server audit specification or database audit specification that maps to the audit.
Enable the audit specification.
Enable the audit.
Read the audit events by using the Windows Event Viewer, Log File Viewer, or the fn_get_audit_file
function.
* (Box 2) Example:
Creating a server audit with a Windows Application log target with options
CREATE SERVER AUDIT HIPAA_Audit
TO APPLICATION_LOG
WITH ( QUEUE_DELAY = 1000, ON_FAILURE = SHUTDOWN);
* Box 4 Example:
/*Creates a server audit specification called “HIPPA_Audit_Specification” that audits failed logins for
the SQL Server audit “HIPPA_Audit” created above.
*/
CREATE SERVER AUDIT SPECIFICATION HIPPA_Audit_Specification
FOR SERVER AUDIT HIPPA_Audit
ADD (FAILED_LOGIN_GROUP);
GO
— Enables the audit.
ALTER SERVER AUDIT HIPAA_Audit
WITH (STATE = ON);
GO

4 Comments on “Which four Transact-SQL statements should you use?”

  1. chip_levis says:

    I don’t believe this answer is correct. Shouldn’t this be “BY dbo” and not “BY public”? Answer should be:

    USE master

    CREATE SERVER AUDIT C_audit
    TO APPLICATION_LOG

    ALTER SERVER AUDIT C_audit
    WITH (STATE = ON)

    USE Contoso

    CREATE DATABASE AUDIT SPECIFICATION C_AuditSpec
    FOR SERVER AUDIT C_audit
    ADD (INSERT ON HumanResources.EmployeeSalary BY dbo ),
    ADD (UPDATE ON HumanResources.EmployeeSalary BY dbo ),
    ADD (DELETE ON HumanResources.EmployeeSalary BY dbo )

    ALTER DATABASE AUDIT SPECIFICATION C_AuditSpec
    WITH (STATE = ON);

    Reference link: “https://msdn.microsoft.com/en-us/library/cc280404.aspx”




    0



    0
    1. 462-learnee says:

      According to the link you’ve provided, BY Accepts a Principal. In the principal page is described:
      “Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable, the user inherits the permissions granted to public on that securable.”
      In this scenario I believe this should be the correct answer.
      “https://msdn.microsoft.com/en-us/library/ms181127.aspx”




      0



      0
      1. Dan says:

        so if a user has been granted to delete then it’s not part of public and will be able to delete and the script will not track his actions because it’s tracking only public




        0



        0
    2. v says:

      The provided answer is correct, your suggestion would only create audit events made ‘BY dbo’. The question does not explicitly request this. Read the description prior to the example you quote in your link. ‘BY public’ would create audit events for all users as per 462-learnee.




      0



      0

Leave a Reply