Data Audit Interview Questions

1. Which of the following tool is used for purpose of data auditing for SQL Server only?

A. ApexSQL

B. SQL Ninja

C. SQL Audit

D. Idera

Correct Answer is : ApexSQL

2. Point out the correct statement:

A. The CREATE SERVER AUDIT statement is in a transaction scope

B. An audit must exist before deleting a server audit specification for it

C. To insert a server audit, principles require the ALTER ANY SERVER AUDIT or the CONTROL SERVER permission

D. None of the mentioned

Correct Answer is : The CREATE SERVER AUDIT statement is in a transaction scope

3. Which of the following object is only available for audit actions in Create Server Audit Specification?

A. Audit Action Type

B. Object Name

C. Audit Name

D. All of the mentioned

Correct Answer is : Object Name

4. Selecting the _________ option can allow unaudited activity which could violate your security policies.

A. Fail

B. Shut down

C. Continue

D. Break

Correct Answer is : Continue

5. Which of the following code creates a server audit called “HIPPA_Audit” with a binary file as the target and no options?

A. CREATE SERVER AUDIT HIPAA_Audit TO SUBJECT ( FILEPATH ='\\SQLPROD_1\Audit\' );

B. CREATE SERVER AUDIT HIPAA_Audit TO FILE ( FILEPATH ='\\SQLPROD_1\Audit\' );

C. CREATE AUDIT HIPAA_Audit TO FILE ( FILEPATH ='\\SQLPROD_1\Audit\' );

D. CREATE SERVER AUDIT HIPAA TO FILE ( FILEPATH ='\\SQLPROD_1\Audit\' );

Correct Answer is : CREATE SERVER AUDIT HIPAA_Audit TO FILE ( FILEPATH ='\\SQLPROD_1\Audit\' );

6. Which of the following code Enables the audit ‘HIPAA_Audit’?

A. ALTER SERVER AUDIT HIPAA_Audit WITH (STATE ==1);

B. ALTER SERVER AUDIT HIPAA_Audit WITH (STATE = OFF);

C. ALTER SERVER AUDIT HIPAA_Audit WITH (STATE = ON);

D. None of the mentioned

Correct Answer is : ALTER SERVER AUDIT HIPAA_Audit WITH (STATE = ON);

7. Which of the following code creates database audit specification ?

A. CREATE AUDIT SPECIFICATION Audit_Pay_Tables FOR SERVER AUDIT Payrole_Security_Audit ADD (SELECT , INSERT ON HumanResources.EmployeePayHistory BY dbo ) WITH (STATE = ON) ;

B. CREATE DATABASE AUDIT Audit_Pay_Tables FOR SERVER AUDIT Payrole_Security_Audit ADD (SELECT , INSERT ON HumanResources.EmployeePayHistory BY dbo ) WITH (STATE = ON) ;

C. CREATE DATABASE AUDIT SPECIFICATION Audit_Pay_Tables FOR SERVER AUDIT Payrole_Security_Audit ADD (SELECT , INSERT ON HumanResources.EmployeePayHistory BY dbo ) WITH (STATE = ON) ;

D. All of the mentioned

Correct Answer is : CREATE DATABASE AUDIT SPECIFICATION Audit_Pay_Tables FOR SERVER AUDIT Payrole_Security_Audit ADD (SELECT , INSERT ON HumanResources.EmployeePayHistory BY dbo ) WITH (STATE = ON) ;

8. Which of the following views log from a file that is named \\serverName\Audit\HIPPA_AUDIT.sqlaudit ?

A. SELECT * FROM sys.fn_audit_file (‘\\serverName\Audit\HIPPA_AUDIT.sqlaudit’, default,default);

B. SELECT * FROM sys.fn_get_audit_file (‘\\serverName\Audit\HIPPA_AUDIT.sqlaudit’, default,default);

C. SELECT * FROM sys.fn_get_audit(‘\\serverName\Audit\HIPPA_AUDIT.sqlaudit’, default,default);

D. None of the mentioned

Correct Answer is : SELECT * FROM sys.fn_get_audit_file (‘\\serverName\Audit\HIPPA_AUDIT.sqlaudit’, default,default);

9. Which of the following statement is used to enable auditing from SQL Server ?

A. auditpol /set /subcategory:”application generated” /success:enable /failure:enable

B. polaudit /set /subcategory:”application generated” /success:enable /failure:enable

C. auditpolenable /set /subcategory:”application generated” /success:enable /failure:enable

D. auditenable /set /subcategory:”application generated” /success:enable /failure:enable

Correct Answer is : auditpol /set /subcategory:”application generated” /success:enable /failure:enable

Data Audit interview questions